Running Macro on many files

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sean - 11 May 2005 20:24 GMT

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

Reply to this Message

Alok - 11 May 2005 22:21 GMT

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

> 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

Reply to this Message

Sean - 12 May 2005 18:31 GMT

Thanks so much Alok.

Reply to this Message

Matt S - 17 May 2008 13:33 GMT

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

> Hi Sean,
> Try this code
[quoted text clipped - 25 lines]
> > Any help would be appreciated.
> > Thanks

Reply to this Message