Macro to open up all files in folder C:\Sales Reports and run macro

[howard]

I have 20 workbooks in C:\Sales Reports

Each on these worksheets have the same macro

Sub UpdateData()

Currently I open each workbook manually and activate the above macro, save the file and close it

I would like a workbook with a macro to open all the files in C:\Sales Reports and to activate the macro

Sub UpdateData()

, save the file and close it once te macro has run

It would be apprciated if someone could provide me with the code to do this

 

[HaHoBe]

Hi howard,

the basics may look like this:

Sub MrE1220869_1613111()
'https://www.mrexcel.com/board/threads/macro-to-open-up-all-files-in-folder-c-sales-reports-and-run-macro.1220869/
Dim strFile As String
Dim wbToOpen As Workbook

Const cstrPath As String = "C:\Sales Reports\"   'mind the backslash at the end!!

strFile = Dir(cstrPath & "*.xls*")

Do While strFile <> ""
  Set wbToOpen = Workbooks.Open(cstrPath & strFile)
  Application.Run ("'" & strFile & "'!UpdateData")
  wbToOpen.Close True
  strFile = Dir
Loop

Set wbToOpen = Nothing

End Sub

Since I do not know how long the called macro takes to run it may be necessary to wait for some time before continuing with the next workbook.

Ciao,
Holger

 

[smozgur]

The following code takes the folder path variable and uses the Scripting.FileSystemObject to loop through files in this folder to open, run the macro called "UpdateData" in each workbook individually, and save and close the workbook. The assumption is every single file in the folder is supposed to be processed with this code - you need to make sure that you only have those 20 workbooks in the folder, otherwise the code should be adjusted to filter files.

Note: The code is using Late Binding, however, it is possible to declare variables in specific types instead of Object type if you'd like to include the Microsoft Scripting FileSystemObject reference. The late binding is a bit slower than the early binding, however, it is preferred if this code is supposed to work in computers that referenced version of the library might not be installed. I don't think it is a problem for this particular library since it is supposed to be installed in Windows systems, but this information here might help future readers as well.

Sub doIt()
Dim fso As Object 'FileSystemObject
Dim fld As Object 'Folder
Dim fil As Object 'File
Dim strPath As String
Dim wrk As Workbook

    strPath = "C:\Sales Reports"

    Application.ScreenUpdating = False 'Optional - to avoid screen flickering
    
    Set fso = CreateObject("Scripting.FileSystemObject") 'New FileSystemObject
    Set fld = fso.GetFolder(strPath)
    For Each fil In fld.Files
        Set wrk = Application.Workbooks.Open(fil.Path)
        Application.Run "'" & wrk.Name & "'!UpdateData"
        wrk.Close True
    Next fil
    
    Application.ScreenUpdating = True
End Sub

Important note: I don't know the UpdateData macro content and how it is running, so I am not sure if this implementation will work without problems with it. So, I suggest making sure to backing up your files before testing this piece of code.

 

[howard]

Many thanks for the help Guys

 

[smozgur]

Many thanks for the help Guys

Great to hear that our solutions help! Thanks for the feedback.

Note: That would be awesome if you could mark the answer post as the solution that helped you best to help future readers (which is post #2 for this thread as I certainly think @HaHoBe's answer is the better one for this question).