Running macro every files in a folder

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
Code:
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!
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
In dealing with Excel files, you should use "ActiveWorkbook", not "ActiveDocument" (that sounds more like a Microsoft Word thing).
 

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

Board Regular
Joined
Sep 23, 2016
Messages
102
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Please answer my first question in the previous post.
 

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
Yes, macro is in same the workbook with public procedure like this

Code:
 Public Sub MyFormat()
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,082,258
Messages
5,364,088
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top