Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 36
Default 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,
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 421
Default 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,


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default 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,

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 36
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
F2 Key not working in Excel 2007 JodieM Excel Discussion (Misc queries) 2 November 18th 10 09:21 PM
Pivot in excel 2003 is not working when open in excel 2007 Jakob[_2_] Excel Worksheet Functions 2 April 15th 08 11:47 PM
Chart allignment issue in 2007 but in 2003 its working fine Venkatesh Charts and Charting in Excel 0 March 18th 08 01:38 PM
Formulas in 2007 not working in 2003 Andrew Excel Worksheet Functions 22 January 28th 08 07:10 PM
Formula not working in excel 2007 Rao Ratan Singh Excel Discussion (Misc queries) 3 January 2nd 08 03:47 PM


All times are GMT +1. The time now is 08:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"