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!
Microsoft ExcelVisual Basic ClassicVB Script

Avatar of undefined
Last Comment
daviddiebel

8/22/2022 - Mon
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
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
daviddiebel

ASKER
This worked for me - thank you!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes