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 :)

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 :)

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 :)

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

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 :)

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