Hi Guys,

I had a Macro which was made using Excel 2003, which looked up Excel files
in a folder, formatted them, saved them, and then repeated the process for
every excel file in the folder.

The Problem is that the I.T in my company upgraded me to Office 2007 today,
and now I cannot get the Loop to work. The excel files that the macro is
trying to open are saved as .xls. Below is the coding I've used (which was
again created using Ron's tips). I'd appreciate if someone could let me know
what I'm doing wrong since I have to create these formatted excel files
(around 450 of them) by end of day tomorrow.

Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("Folder location")
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path

*complete formatting*

End If

Next file
Set oFSO = Nothing

Thanks Guys,

Hi Pman,

In what way does the code not work?

If you encounter an error, what is the
error number and message, and which
line of code is highlighted?



---
Regards.
Norman


"Pman" wrote in message
...
Hi Guys,

I had a Macro which was made using Excel 2003, which looked up Excel files
in a folder, formatted them, saved them, and then repeated the process for
every excel file in the folder.

The Problem is that the I.T in my company upgraded me to Office 2007
today,
and now I cannot get the Loop to work. The excel files that the macro is
trying to open are saved as .xls. Below is the coding I've used (which was
again created using Ron's tips). I'd appreciate if someone could let me
know
what I'm doing wrong since I have to create these formatted excel files
(around 450 of them) by end of day tomorrow.

Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("Folder location")
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path

*complete formatting*

End If

Next file
Set oFSO = Nothing

Thanks Guys,

See this example
http://www.rondebruin.nl/copy4.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Pman" wrote in message ...
Hi Guys,

I had a Macro which was made using Excel 2003, which looked up Excel files
in a folder, formatted them, saved them, and then repeated the process for
every excel file in the folder.

The Problem is that the I.T in my company upgraded me to Office 2007 today,
and now I cannot get the Loop to work. The excel files that the macro is
trying to open are saved as .xls. Below is the coding I've used (which was
again created using Ron's tips). I'd appreciate if someone could let me know
what I'm doing wrong since I have to create these formatted excel files
(around 450 of them) by end of day tomorrow.

Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("Folder location")
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path

*complete formatting*

End If

Next file
Set oFSO = Nothing

Thanks Guys,

Thanks for your replies Norman and Ron. The Macro simply wasn't picking up
files to format. But I figured out why......I changed the filetype the Macro
looked up from "Microsoft Excel" to plain "Excel" and it picked up the files
to format :)

Thanks,

Prash

hi Pman

That's why I check the extensions

Now with the new formats in 2007 you also have more control.
You can do only xls or xlsx or xlsm or ??



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Pman" wrote in message ...

Thanks for your replies Norman and Ron. The Macro simply wasn't picking up
files to format. But I figured out why......I changed the filetype the Macro
looked up from "Microsoft Excel" to plain "Excel" and it picked up the files
to format :)

Thanks,

Prash