troubleshooting Question

How to Run Macro on a List of Workbooks

Avatar of daviddiebel
daviddiebel asked on
Visual Basic ClassicMicrosoft ExcelVB Script
3 Comments1 Solution460 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
NorieAnalyst Assistant
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros