ایجاد تغییرات در گروهی از کتاب های کاری در اکسل

Question

با گذشت زمان، ایجاد و جمع آوری تعداد زیادی کتاب کار اکسل بسیار آسان است. فرض کنید که شما یک دسته کامل کتاب کار دارید که باید همان تغییر را در آنها ایجاد کنید. به عنوان مثال، ممکن است لازم باشد مقدار ذخیره شده در سلول A10 هر یک از کاربرگ های هر یک از کتاب های کاری را تغییر دهید.

اگر فقط چند کتاب کار برای تغییر داشتید، کار بسیار آسان است: هر کتاب کار را بارگیری کنید و به نوبه خود تغییر را در هر یک از آنها ایجاد کنید. اگر چند صد کتاب کار دارید که باید در آنها تغییر ایجاد شود، آن وقت این کار دشوارتر می شود.

اگر پیش‌بینی می‌کنید که فقط یک بار باید این کار را انجام دهید، ساده‌ترین راه حل این است که یک فایل متنی ایجاد کنید که حاوی مسیر و نام فایل هر یک از کتاب‌های کاری، یک کتاب کار در هر خط باشد. به عنوان مثال، ممکن است با فایلی مواجه شوید که دارای ورودی هایی مانند این باشد:

c:myfilesfirst workbook.xlsx
c:myfilessecond workbook.xlsx
c:myfiles	hird workbook.xlsx

فایل می تواند به تعداد خطوط لازم در خود داشته باشد. واقعا مهم نیست نکته مهم این است که هر خط یک مسیر و نام فایل معتبر باشد و هیچ خط خالی در فایل وجود نداشته باشد.

شما به راحتی می توانید چنین فایلی را با نمایش یک پنجره خط فرمان، پیمایش به دایرکتوری حاوی Workbook ها و صدور دستور زیر ایجاد کنید:

dir /b > myfilelist.txt

هر فایل در دایرکتوری به فایل myfilelist.txt ختم می شود. شما باید فایل متنی را در یک ویرایشگر متن بارگیری کنید و آن را بررسی کنید تا بتوانید ورودی های اضافی را حذف کنید. (به عنوان مثال، myfilelist.txt در فهرست قرار می گیرد.) همچنین باید نام مسیر را به ابتدای هر خط در فایل اضافه کنید.

پس از تکمیل فایل، می‌توانید اکسل را راه‌اندازی کنید و از یک ماکرو برای خواندن فایل متنی استفاده کنید، هر کتاب کار فهرست شده در فایل متنی را بارگیری کنید، هر کاربرگ آن کتاب کار را مرور کنید، تغییر مناسب را ایجاد کنید و کتاب کار را ذخیره کنید. ماکرو زیر این وظایف را به خوبی انجام می دهد.

Sub ChangeFiles1()
    Dim sFilename As String
    Dim wks As Worksheet

    Open "c:myfilesmyfilelist.txt" For Input As #1
    Do While Not EOF(1)
        Input #1, sFilename   Get workbook path and name
        Workbooks.Open sFilename

        With ActiveWorkbook
            For Each wks In .Worksheets
                 Specify the change to make
                wks.Range("A1").Value = "A1 Changed"
            Next
        End With

        ActiveWorkbook.Close SaveChanges:=True
    Loop
    Close #1
End Sub

در حالی که این رویکرد در صورتی که فقط یک دسته از فایل‌های کتاب کار را پردازش کنید عالی عمل می‌کند، اگر پیش‌بینی کنید که نیاز به ایجاد چنین تغییراتی در آینده داشته باشید، می‌توان آن را بسیار انعطاف‌پذیرتر کرد. البته بزرگترین دردسر این است که هر بار که می خواهید دسته ای از فایل ها را پردازش کنید، فایل myfilelist.txt را کنار هم قرار دهید. اگر ماکرو بتواند به سادگی از یک دایرکتوری استفاده کند و سپس هر کتاب کار را از آن دایرکتوری بارگیری کند، انعطاف پذیری اضافه می شود.

Sub ChangeFiles2()
    Dim MyPath As String
    Dim MyFile As String
    Dim dirName As String
    Dim wks As Worksheet

     Change directory path as desired
    dirName = "c:myfiles"

    MyPath = dirName & "*.xlsx"
    MyFile = Dir(MyPath)
    If MyFile > "" Then MyFile = dirName & MyFile

    Do While MyFile <> ""
        If Len(MyFile) = 0 Then Exit Do 

        Workbooks.Open MyFile

        With ActiveWorkbook
            For Each wks In .Worksheets
                 Specify the change to make
                wks.Range("A1").Value = "A1 Changed"
            Next
        End With

        ActiveWorkbook.Close SaveChanges:=True

        MyFile = Dir
        If MyFile > "" Then MyFile = dirName & MyFile
    Loop
End Sub 

این ماکرو از هر دایرکتوری که برای متغیر dirName مشخص می کنید استفاده می کند. هر فایل کتاب کار (که با پسوند Xlsx ختم می شود) بارگیری و پردازش می شود.

روش دیگر این است که ماکرو از کاربر بپرسد کدام دایرکتوری باید پردازش شود. برای انجام این کار می توانید از کادر محاوره ای استاندارد Excel File به روشی که در ماکرو زیر نشان داده شده است استفاده کنید.

Public Sub ChangeFiles3()
    Dim MyPath As String
    Dim MyFile As String
    Dim dirName As String

    With Application.FileDialog(msoFileDialogFolderPicker)
         Optional: set folder to start in
        .InitialFileName = "C:Excel"
        .Title = "Select the folder to process"
        If .Show = True Then
            dirName = .SelectedItems(1)
        End If
    End With

    MyPath = dirName & "*.xlsx"
    myFile = Dir(MyPath)
    If MyFile > "" Then MyFile = dirName & MyFile

    Do While MyFile <> ""
        If Len(MyFile) = 0 Then Exit Do 

        Workbooks.Open MyFile

        With ActiveWorkbook
            For Each wks In .Worksheets
                 Specify the change to make
                wks.Range("A1").Value = "A1 Changed"
            Next
        End With

        ActiveWorkbook.Close SaveChanges:=True

        MyFile = Dir
        If MyFile > "" Then MyFile = dirName & MyFile
    Loop
End Sub