VBA Not Working

[AllDay]

My goal here is to open files in a separate folder, run the macro in each file, close and save it. The macro in that workbook updates 15 power queries.

The Code Completes The Loop and opens all files, changes the value in A1 and saves them. It however is not running the macro from the other work book to update those power queries. (The macro in that workbook when run in that book does update the power queries though). It is executed by clicking a button and don't know if that is causing the hangup. Any help is greatly appreciated.

Sub AllFiles()
Dim folderPath as String
Dim filename As String

folderPath = "My File Path"

If Right (folderPath, 1) <>"\" Then folderPath = folderPath + "\"

filename = Dir (folderPath & "*.xlsm")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)
ActiveWorkbook.Sheets("My Sheet Name").Activate
Range("A1").Value = "My Value"

Call MyMacro

filename = Dir

ActiveWorkbook.Close SaveChanges:=True

Loop

Application.ScreenUpdating = True

MsgBox "Completed"

End Sub

 

[Fluff]

If your MyMacro is in a standard module, try

Application.Run ("'" & wb.Name & "'!MyMacro")

Instead of

Call MyMacro

 

[AllDay]

I have 200 sequentially named files. I am assuming wb.Name is wb."Name of each workbook in folder"? What would be best way to tackle that?

 

[Fluff]

As each file is opened, the Mymacro in that file will run

 

[AllDay]

Awesome that got it done. Now as I stated this updates 15 Power Queries. Is there a way create an error log if one of the queries doesn't update?

 

[Fluff]

Is there a way create an error log if one of the queries doesn't update?

I have never used power queries, so unfortunately, cannot help on that.