Running Macro on many files

[Guest]

Hi,
I have about 50 files in a folder and I want to run my macro on each file.
Is there a way to automate this?
Any help would be appreciated.
Thanks

 

[Guest]

Hi Sean,
Try this code

Sub ProcessAllFiles()

Dim sFile$
Const path = "C:\Temp\Temp1\"

sFile = Dir(path & "*.xls")
Do While sFile <> ""
'the new workbook will become active
Workbooks.Open (path & sFile)
'run your macro here
'however make sure that the new workbook
'remains active throughout the macro
Macro1
ActiveWorkbook.Close savechanges:=True
sFile = Dir
Loop

End Sub

Alok Joshi

 

[Guest]

Thanks so much Alok.

 

[Matt S]

Alok,

I've tried your code on one of my macros, but I cannot seem to get it to
work. I have tons of runlogs that are generated and would like to implement
your code. The runlogs are incremented by *.L0, *.L1, *.L2, etc. where the
number could be anywhere between 0 to 10. What did I do wrong in the code
below? If I step through the code, it just goes right over the While loop,
even if I do change a few files to *.xls.

Thanks,
<3 Matt

Sub ProcessAllFiles()

Dim sFile$
Const path = "C:\Temp\Temp1\"

sFile = Dir(path & "*.*")
Do While sFile <> ""
Workbooks.Open (path & sFile)
HELO_Macro
ActiveWorkbook.Close savechanges:=True
sFile = Dir
Loop

End Sub