Running macro every files in a folder

[jaik22]

Sub MacroRun()
Dim file
Dim path As String




path = "C:\Users\Desktop\TEST\"




file = Dir(path & "*.xlsm")
Do While file <> ""
Documents.Open Filename:=path & file




 Call Format




ActiveDocument.Save
ActiveDocument.Close


file = Dir()
Loop
End Sub

This is my code for running a macro for entire excel files in a folder, and Format is the macro that I want to call. It seems correct, but macro does not work at all. Is there any mistake that I made?

Thank you!

 

[Joe4]

In dealing with Excel files, you should use "ActiveWorkbook", not "ActiveDocument" (that sounds more like a Microsoft Word thing).

 

[jaik22]

Thanks for the reply Joe4,
Good catch, that was a typo. However, that wasn't the issue. I can't call the module to run the macro... Do you have any idea?

 

[Joe4]

Is the macro a public procedure in the same workbook?

Also, you should never used reserved words like "Format" for the name of your procedures, functions, and variables. Using reserved words can cause confusion and unexpected results, as Excel may not be able to determine if you are referring to your procedures, functions, and variables or their functions, properties, and objects. Try renaming your procedure to something like "MyFormat".

 

[jaik22]

Thanks for reply,
I changed format to Myformat but it still gives me an error. "Expected variable or procedure, not module" are you familiar with this error?

 

[Joe4]

Please answer my first question in the previous post.

 

[jaik22]

Yes, macro is in same the workbook with public procedure like this

 Public Sub MyFormat()

 

[Joe4]

Have you put it in a Standard Module, or one of the Sheet or Workbook modules?

I would recommend using F8 to step through your code line-by-line to see what is happening each step of the way. If you resize your VB Editor screen, you can watch both the sheet and VBA code at the same time to see what is happening.

 

[jaik22]

Thanks I found the error, it was a typo.....