Excel 2007 Macro Help (Excel 2003 not working in 2007)
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, |
Excel 2007 Macro Help (Excel 2003 not working in 2007)
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, |
Excel 2007 Macro Help (Excel 2003 not working in 2007)
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, |
Excel 2007 Macro Help (Excel 2003 not working in 2007)
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 |
Excel 2007 Macro Help (Excel 2003 not working in 2007)
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 |
All times are GMT +1. The time now is 05:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com