Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
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 -- Gary''s Student - gsnu200786 "Matt S" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
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? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200786 "Matt S" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
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. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Matt S" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
'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" 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. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Matt S" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
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" wrote: '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" 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. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Matt S" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro on multiple files
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\MyFileNam e", _ FileFormat:=xlWorkbookNormal -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Matt S" 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running Macro on many files | Excel Programming | |||
Files won't open after running a macro | Excel Programming | |||
Running a macro on mulitple files | Excel Programming | |||
opening multiple files using VB and running a mcaro in excel | Excel Programming | |||
Import multiple files macro can't find files | Excel Programming |