File Save As Macro, Excel 2003 and 2007

[Gerald Higgins]

I've been using Excel 2003 until last week, when I got switched to 2007.
My colleagues are still all on 2003.

I have a macro that has been running fine in 2003, but testing it in 2007 it now falls over.

It does a number of things, but at the point where it falls over, it is saving the single current worksheet of the current multi-sheet workbook, as a standalone file, with a single sheet.

This code works in 2003....

ActiveWorkbook.SaveAs Filename:= "C:\Filename.xls", FileFormat:= xlExcel4

but it falls over in 2007, with an error message of

Run time error '1004'
Method 'Save as' of object '_Workbook' failed

I'm guessing this is because 2007 doesn't like me saving it in xlExcel4 format, and when I try to do it manually, I can't find that option on the Save As menu.

So, QUESTION, does anyone know what code I could use to achieve the same result, and preferably for it to work in BOTH 2003 AND 2007 ?

Thanks in advance for any help . . .

 

[VoG]

Hi Gerald. Try

ActiveWorkbook.SaveAs Filename:="C:\Filename.xls", FileFormat:=56

See http://www.rondebruin.nl/saveas.htm

 

[Gerald Higgins]

Very quick answer VoG, thanks very much
It will take me a while to test it in both 2003 and 2007, I'll post back when I've done that.

Cheers. . .

 

[Gerald Higgins]

VoG, thanks but that did not work.

In 2003, it falls over at exactly the same line, with exactly the same error message as before, so I'm guessing that Excel 2003 does not like the "56" file format.

In 2007, the code runs without error, but it's not doing the right thing, it's saving the entire multi-worksheet workbook, rather than just the single current worksheet.

Any ideas ?

In the meantime, I'm going to experiment with the macro recorder in 2007.

Thanks in advance for any further help.

 

[Jon von der Heyden]

What happens if you ditch the fileformat argument altogether? It should be necessary and since the file extension is '.xls' it will save it as 97-2003 workbook format as default.

 

[Jon von der Heyden]

Or you change the saveas format according to the version:

If Val(Application.Version)>=12 Then
    ActiveWorkbook.SaveAs Filename:="C:\Filename.xls", FileFormat:=56
Else
    ActiveWorkbook.SaveAs Filename:= "C:\Filename.xls", FileFormat:= xlExcel4
End If

 

[Gerald Higgins]

Thanks Jon.

Your suggestion in post #6 would probably work in 2003, but not in 2007. The "56" code does not have the desired effect in 2007 of saving only the current worksheet.

Maybe I'm not making myself clear on the requirements, apologies.

The main workbook has many worksheets (about a dozen).
The macro should save the current single worksheet in an Excel file, with a new name.
The macro then goes off and does other things in the main workbook.

In 2007, the "56" code runs without error, but instead of saving just the current worksheet, it saves the entire multi-sheet workbook, which is not what I need.

Thanks again in advance for any help.

 

[Jon von der Heyden]

Oh I see.

So you could copy the sheet to a new workbook and save that:

Sheets(1).Copy
With ActiveWorkbook
    .SaveAs Filename="C:\Test.xls"
    .Close
End With

 

[Andrew Poulsom]

Saving as Excel 4.0 Worksheet is deprecated in Excel 2007:

http://blogs.office.com/b/microsoft-excel/archive/2006/08/24/deprecated-features-for-excel-2007.aspx

 

[Gerald Higgins]

Thanks Andrew.

I'm not precious about the Excel4 format - if I have to use something else that would be OK.
I picked on Excel4 because it seemed to work, i.e. gave me a single sheet file from the multi-sheet original.