Excel 2007 Macro Help (Excel 2003 not working in 2007)

[Pman]

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,

 

[Norman Jones]

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?

 

[Ron de Bruin]

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

 

[Pman]

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

 

[Ron de Bruin]

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 ??