Running macro on multiple files

[Matt S]

I posted this earlier on an old thread, but I guess it doesn't bump the old
thread for people to see. I hope I'm not breaking any rules or whatever, so
please ignore this post if I am.

Thanks,
Matt

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. I made sure that C:\Temp\Temp1 is
where my files are.

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

 

[Jim Cone]

I changed "path" to sPath and used "C:\WINDOWS\Temp" as the path
and it picked up every file in the folder.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Matt S" <[email protected]>
wrote in message
I posted this earlier on an old thread, but I guess it doesn't bump the old
thread for people to see. I hope I'm not breaking any rules or whatever, so
please ignore this post if I am.
Thanks,
Matt

-snip-
"If I step through the code, it just goes right over the While loop,"
-snip-

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

 

[Gary''s Student]

Hi Matt:

I tried you code. It works. Just make sure that ALL the files in
C:\Temp\Temp1 are .xls and that the problem is not in HELO_Macro. I used for
HELO_Macro:

Sub HELO_Macro()
Cells(1, 1) = 1
End Sub

 

[Matt S]

The files in the folder are not *.xls. They are semi-colon delimited data
files that I can open with excel and delimit. That's the first step in the
HELO macro. What do I do in my case where I do not have *.xls files but
these *.L0, *.L1, etc?

 

[Matt S]

Jim,

I'm new to macros... can you please paste your code so I can see where
you've changed path to sPath?

Thanks,
Matt

 

[Jim Cone]

'As requested...
'--
Sub ProcessAllFiles()
Dim sFile As String
Const sPath As String = "C:\WINDOWS\Temp\"

sFile = Dir(sPath & "*.*")
Do While sFile <> ""
MsgBox sFile
' Workbooks.Open (sPath & sFile)
' HELO_Macro
' ActiveWorkbook.Close savechanges:=True
sFile = Dir
Loop
End Sub
;--
Jim Cone


"Matt S" <[email protected]>
wrote in message
Jim,
I'm new to macros... can you please paste your code so I can see where
you've changed path to sPath?
Thanks,
Matt

 

[Matt S]

Thank you Jim! This worked perfectly. How do I force it to save the new
files into a new folder and convert the files to excel workbooks? Seems when
it saves the files now, they automatically save as 1-sheet files.

Thanks!
Matt

 

[Jim Cone]

A one sheet workbook is still a workbook.

Look at "SaveAs" in Excel VBA help. The new folder has to exist...
ActiveWorkbook.SaveAs _
Filename:="C:\WINDOWS\Temp\NewFolderName\MyFileName", _
FileFormat:=xlWorkbookNormal
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Matt S" <[email protected]>
wrote in message
Thank you Jim! This worked perfectly. How do I force it to save the new
files into a new folder and convert the files to excel workbooks? Seems when
it saves the files now, they automatically save as 1-sheet files.

Thanks!
Matt