Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all workbooks inside a folder, run macro and save them?
Hi,
I have a folder that has more than 400 excel files, and all of them contain unformatted data. I have a macro for formatting this data that runs as required. The problem is I need to open each file individually and run the macro to format the data in each file. So is there a way I can open all the workbooks inside the folder, run the macro that I have already for all of them, and then save the workbook? the data is contained in Sheet 1 in all the workbooks. Thanks :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all workbooks inside a folder, run macro and save them?
Sub LoopFolders() Dim oFSODim Folder As Object Dim Files As Object Dim file As Object Set oFSO = CreateObject("Scripting.FileSystemObject") Set Folder = oFSO.GetFolder("c:\MyTest") For Each file In Folder.Files If file.Type Like "*Microsoft Excel*" Then Workbooks.Open Filename:=file.Path '<<<<< run macro here on Activeworkbook Activeworkbook.Close SaveChanges:=False End If Next file Set oFSO = Nothing End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pman" wrote in message ... Hi, I have a folder that has more than 400 excel files, and all of them contain unformatted data. I have a macro for formatting this data that runs as required. The problem is I need to open each file individually and run the macro to format the data in each file. So is there a way I can open all the workbooks inside the folder, run the macro that I have already for all of them, and then save the workbook? the data is contained in Sheet 1 in all the workbooks. Thanks :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all workbooks inside a folder, run macro and save them?
Thanks for the quick reply Bob, but when I run the macro I get an error
message, and the text "Dim oFSODim Folder As Object" is highlighted when I go into De-bug mode. When I run the LoopFolders macro, do I have to open my 1st workbook and run the macro in it? Thanks, Peter. "Bob Phillips" wrote: Sub LoopFolders() Dim oFSODim Folder As Object Dim Files As Object Dim file As Object Set oFSO = CreateObject("Scripting.FileSystemObject") Set Folder = oFSO.GetFolder("c:\MyTest") For Each file In Folder.Files If file.Type Like "*Microsoft Excel*" Then Workbooks.Open Filename:=file.Path '<<<<< run macro here on Activeworkbook Activeworkbook.Close SaveChanges:=False End If Next file Set oFSO = Nothing End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pman" wrote in message ... Hi, I have a folder that has more than 400 excel files, and all of them contain unformatted data. I have a macro for formatting this data that runs as required. The problem is I need to open each file individually and run the macro to format the data in each file. So is there a way I can open all the workbooks inside the folder, run the macro that I have already for all of them, and then save the workbook? the data is contained in Sheet 1 in all the workbooks. Thanks :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all workbooks inside a folder, run macro and save them?
That should be two lines:
Dim oFSO Dim Folder As Object Pman wrote: Thanks for the quick reply Bob, but when I run the macro I get an error message, and the text "Dim oFSODim Folder As Object" is highlighted when I go into De-bug mode. When I run the LoopFolders macro, do I have to open my 1st workbook and run the macro in it? Thanks, Peter. "Bob Phillips" wrote: Sub LoopFolders() Dim oFSODim Folder As Object Dim Files As Object Dim file As Object Set oFSO = CreateObject("Scripting.FileSystemObject") Set Folder = oFSO.GetFolder("c:\MyTest") For Each file In Folder.Files If file.Type Like "*Microsoft Excel*" Then Workbooks.Open Filename:=file.Path '<<<<< run macro here on Activeworkbook Activeworkbook.Close SaveChanges:=False End If Next file Set oFSO = Nothing End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pman" wrote in message ... Hi, I have a folder that has more than 400 excel files, and all of them contain unformatted data. I have a macro for formatting this data that runs as required. The problem is I need to open each file individually and run the macro to format the data in each file. So is there a way I can open all the workbooks inside the folder, run the macro that I have already for all of them, and then save the workbook? the data is contained in Sheet 1 in all the workbooks. Thanks :) -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all workbooks inside a folder, run macro and save them?
Thanks Bob :)
"Bob Phillips" wrote: Sub LoopFolders() Dim oFSODim Folder As Object Dim Files As Object Dim file As Object Set oFSO = CreateObject("Scripting.FileSystemObject") Set Folder = oFSO.GetFolder("c:\MyTest") For Each file In Folder.Files If file.Type Like "*Microsoft Excel*" Then Workbooks.Open Filename:=file.Path '<<<<< run macro here on Activeworkbook Activeworkbook.Close SaveChanges:=False End If Next file Set oFSO = Nothing End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pman" wrote in message ... Hi, I have a folder that has more than 400 excel files, and all of them contain unformatted data. I have a macro for formatting this data that runs as required. The problem is I need to open each file individually and run the macro to format the data in each file. So is there a way I can open all the workbooks inside the folder, run the macro that I have already for all of them, and then save the workbook? the data is contained in Sheet 1 in all the workbooks. Thanks :) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open all workbooks inside a folder, run macro and save them?
Thanks Dave :)
"Dave Peterson" wrote: That should be two lines: Dim oFSO Dim Folder As Object Pman wrote: Thanks for the quick reply Bob, but when I run the macro I get an error message, and the text "Dim oFSODim Folder As Object" is highlighted when I go into De-bug mode. When I run the LoopFolders macro, do I have to open my 1st workbook and run the macro in it? Thanks, Peter. "Bob Phillips" wrote: Sub LoopFolders() Dim oFSODim Folder As Object Dim Files As Object Dim file As Object Set oFSO = CreateObject("Scripting.FileSystemObject") Set Folder = oFSO.GetFolder("c:\MyTest") For Each file In Folder.Files If file.Type Like "*Microsoft Excel*" Then Workbooks.Open Filename:=file.Path '<<<<< run macro here on Activeworkbook Activeworkbook.Close SaveChanges:=False End If Next file Set oFSO = Nothing End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pman" wrote in message ... Hi, I have a folder that has more than 400 excel files, and all of them contain unformatted data. I have a macro for formatting this data that runs as required. The problem is I need to open each file individually and run the macro to format the data in each file. So is there a way I can open all the workbooks inside the folder, run the macro that I have already for all of them, and then save the workbook? the data is contained in Sheet 1 in all the workbooks. Thanks :) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to open, Update links, save and close workbooks | Excel Discussion (Misc queries) | |||
open all workbooks in a folder | Excel Programming | |||
open file from folder save in new folder | Excel Programming | |||
Macro to Open all workbooks in a folder and change default font | Excel Programming | |||
Using VBA in Excel to Make a Folder to Save Workbooks in | Excel Programming |