Running a macro on all open worksheets.
Posted by Chris B on August 10, 2001 11:54 AM
Hello...
I have a a BUNCH of csv files that need to be reformatted for use by another program. I have a macro that does this manipulation perfectly! What I am looking for is a way to either run this macro on all csv files in a directory or I can open them, say 50 at a time, and then run my reformat macro. Also, this method would need to save and close each csv file once it was done. Thanks Chris
Posted by gregc on August 10, 2001 12:18 PM
Dir Function
You need to use the dir function. You can use it to loop through all the files in a folder. If you need I can give you some code I have, but it is a bit confusing. I have a hard time figuring out what I did when I read it.
Posted by John on August 10, 2001 1:34 PM
sample code
Here is some sample code that Ivan provided for me from an earlier post ...
It should serve as a fairly straightforward starting point for you. Hope it helps
-John
Dim F
Dim x
F = Dir("C:\Excelfiles\Useful\*.xls")
ComboBox1.Clear
Do While Len(F) > 0
ComboBox1.AddItem F
F = Dir()
x = x + 1
Loop
Posted by Jerid on August 10, 2001 2:04 PM
Re: Dir Function
Does this help?
Private Sub GetFiles()
Dim x As Integer
'Gets the input files from the user, assign them to the array sfiles
sFiles = Application.GetOpenFilename("Comma Separated (*.csv), *.csv", , "Files To be Processed", MultiSelect:=True)
For x = 1 To UBound(sFiles)
'Place you code here
'Open file sFiles(x)
'Process file sFiles(x) and so on
Next x
End Sub
