[ Loop to run macros from other workbooks ]

I would greatly appreciate your help with a macro that I am trying to create.

I have a pathway that looks as follows: K:\XXX\XXX\XXX\Module 1

Module 1 is a folder that contains a bunch of xlsm files named with a number (i.e. 100.xlsm, 110.xlsm, and so forth)

I would like to create a loop that:

1. Runs the macro in workbook 100.xlsm;
2. Saves the 100.xlsm (NOT "save as") when the macro is done running;
3. Closes the saved xlsm, moves on to the next file (i.e. 110.xlsm), and repeats the same steps.

Before running the loop, I would like to create a statement that stores the names of those xlsm files.

The macro below may give you an idea of what I am after. There are indeed several errors.

Sub update()

Dim path As String path = "K:\XXX\XXX\XXX\Module 1" 

Dim list() As Integer 
List=(100, 110, 137, 140)

For Each n As Integer In list   

  Application.Run (path & "\" &n.xslm!refresh)   
  Save WORKBOOK  
  Close WORKBOOK

Next

End Sub

Answer 1

I think something like the code below will achieve what you are wanting to do.

Note that the code first opens the workbook whose macro you want to run. You can then run the macro in that opened workbook from your original workbook with the Application.Run() command, e.g.

Application.Run("book1.xlsm!mymacro"), or
result = Application.Run("book1.xlsm!mymacro", "Hello", 20)

The second example calls a macro that requires a string paramater and an integer parameter.

The fuller example below opens some specific workbooks called 100.xlsm, 110.xlsm, etc and then runs a macro in each of them called SayHelloMessage. I hope this helps.

Sub RunMacrosInOtherWorkbooks()

   Dim wbpath As String          'path where the workbooks containing the macros you want to run are saved
   Dim wbnames() As String       'array containing names of workbooks whose macros you want to run
   Dim wbTarget As Workbook      'current workbook who macro is being run
   Dim macroname As String       'name of macro being run

   wbpath = "C:\Test"
   wbnames() = Split("100.xlsm,110.xlsm,137.xlsm,140.xlsm", ",")  'Just one way of creating the list of workbooks.
   macroname = "SayHelloMessage"

   Dim i As Integer
   Dim result As Variant
   For i = 0 To UBound(wbnames)
      Set wbTarget = Workbooks.Open(wbpath & "\" & wbnames(i))
      result = Application.Run(wbTarget.Name & "!" & macroname)
      ' result = Application.Run(wbTarget.Name & "!" & macroname, 76)  'calling a subroutine or function with an argument.  You need something to catch a return code
      wbTarget.Save
      wbTarget.Close
   Next

End Sub