Link to home
Start Free TrialLog in
Avatar of daviddiebel
daviddiebel

asked on

How to Run Macro on a List of Workbooks

If I am running a macro from a certain workbook in which I have a list of other Excel workbooks (including filepath), is there are way to run a macro to be executed for all of the listed workbooks?  An example list would be:

C:\Files\A.xlsx
C:\Files\B.xlsx
C:\Files\C.xlsx
C:\Files\D.xlsx
C:\Files\E.xlsx

Let us say that this list will vary in length, and will always be in Sheet1, column A of the macro-bearing workbook.  I want a macro to call the process to be executed for each of these files.  Let's call this other macro/process "FileProcess."

I have a macro that will loop through all files in a folder and run the FileProcess macro, but that is not what I want, because I want flexibility about what files to process, and from what location:

Public Sub Loop()
   
   Dim Filename As String
   Dim SourceWorkbook As Workbook
   Dim SourceWorksheet As Worksheet
   
   'Change below to suit
   Const Folder = "C:\Files"
   
   Filename = Dir(Folder & "\*.XLSX")
   Do While Filename <> ""
      Workbooks.Open Folder & "\" & Filename
     
        FileProcess
         
      ActiveWorkbook.Close True
      Filename = Dir
   Loop
   
End Sub

Thank you for your assistance!
Avatar of Norie
Norie

You could add a reference to the workbook(s) you are opening like this and then use it in the rest of the code.
Set wbProcess = Workbooks.Open (Folder & "\" & Filename)

' FileProcess using wbProcess to workbook being processed

wbProcess.Close True

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of daviddiebel

ASKER

This worked for me - thank you!