How to Run Macro on a List of Workbooks

daviddiebel
daviddiebel used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
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

Analyst Assistant
Commented:
Actually, just reread the question.

If you want to read the filenames from a list.
Dim rng As Range
Dim strFileName As String

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

While rng.Value <> ""

       strFileName = rng.Value
       Set wbProcess = Workbooks.Open(strFilename)
      
       ' FileProcess
       wbProcess.Close True
       Set rng = rng.Offset(1)

Open in new window

Author

Commented:
This worked for me - thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial