Running macro on multiple files

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt S - 17 May 2008 13:46 GMT

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

Reply to this Message

Jim Cone - 17 May 2008 14:19 GMT

I changed "path" to sPath and used "C:\WINDOWS\Temp" as the path
and it picked up every file in the folder.

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Matt S" <MattS@discussions.microsoft.com>
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

Reply to this Message

Matt S - 17 May 2008 14:35 GMT

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

>
> I changed "path" to sPath and used "C:\WINDOWS\Temp" as the path
> and it picked up every file in the folder.

Reply to this Message

Jim Cone - 17 May 2008 14:49 GMT

'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" <MattS@discussions.microsoft.com>
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

"Jim Cone" wrote:

> I changed "path" to sPath and used "C:\WINDOWS\Temp" as the path
> and it picked up every file in the folder.

> "Matt S" <MattS@discussions.microsoft.com>
> wrote in message
[quoted text clipped - 19 lines]
> Loop
> End Sub

Reply to this Message

Matt S - 17 May 2008 15:17 GMT

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

> 'As requested...
> '--
[quoted text clipped - 49 lines]
> > Loop
> > End Sub

Reply to this Message

Jim Cone - 17 May 2008 15:44 GMT

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

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Matt S" <MattS@discussions.microsoft.com>
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

Reply to this Message

Gary''s Student - 17 May 2008 14:22 GMT

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

Signature

Gary''s Student - gsnu200786

> 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
[quoted text clipped - 29 lines]
>
> End Sub

Reply to this Message

Matt S - 17 May 2008 14:32 GMT

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?

> Hi Matt:
>
[quoted text clipped - 39 lines]
> >
> > End Sub

Reply to this Message