Open Workbooks in A Directory Run a Macro
Adding macros to a workbook can save time with multiple repetitive tasks. Looping through directories is a good place to start with Excel VBA. If you have a list of templates set up which all have the same macro this can become problematic if you have to open every workbook and run the macro on that workbook then close each workbook. It defeats the purpose of creating an efficient time saving process. A simple work around is to use VBA to call macros in a workbook. This way the files are opened, the Macros run and the files are then closed.
The following procedure will open all of the files in a specified directory and run a macro called Test. It assumes the macro in the file is called Test or the procedure will not run as anticipated.
Sub OpennRunCode() 'Open files run Excel VBA macro
Dim sFil As String
Dim owb As Workbook
sFil=Dir(sPath & "*.xl*") 'Captures all XL files in a directory.
Do While sFil <> "" 'Loop through all files in Folder
Application.Run ("'" + sFil + "'!Test")
sFil=Dir
End Sub
The above Excel VBA macro should work on your dataset provided the path is correct and the name of the macro is correct for your example.