Syntax for saving Excel 2007 as 2003 2

[LisaBee]

I have this wonderful spreadsheet that the company has been using with very few issues . . . until they started upgrading some of the users to 2007. Now when a 2007 user saves a file using the macro in the 2003 spreadsheet (which ultimately creates a copy of the worksheet to a new book, removes some autoshapes and buttons, then saves the new copy to the server), the only thing that appears on the server is the file name, and the size is 0k.

I currently use:

ActiveWorkbook.SaveAs FileName:=ClosingFileName

where the ClosingFileName includes the full path to the server, file name, and .xls extension. Since I don't have 2007 to play around with yet, my first best guess is that the problem lies with the copy itself. I've read that the user can manually set 2007 to default to save as an earlier version (but knowing that I can't trust users to do that -- and unfortunately won't get a chance to test that until Monday when a user is available since I don't have 2007), is it possible to include that in the macro so it's done for them . . . at least for that one specific sheet? If it's possible, I would greatly appreciate the syntax for that.

I've already confirmed read/write permissions are established correctly, and tried saving the file with no extension (it wouldn't save), and with a .xlsx extension (which saved the file name, but again, 0k in size).

Thanks very much!

 

[RoyVidar]

Check out

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

See how the version of Excel is determined, and how to save in different versions.

Roy-Vidar

 

[HughLerwill]

If Val(.Version) < 12 Then
.ActiveWorkbook.SaveAs MyFileName$ & ".xls"
Else
.ActiveWorkbook.SaveAs MyFileName$ & ".xls", 56 '56 = xlExcel8 in Excel 12: versions of Excel prev to 12 do not understand the xlExcel8 constant
End If

Should do it.

 

[HughLerwill]

My bad was for vb6; should be less dotty for Excel VBA

If Val(Version) < 12 Then
ActiveWorkbook.SaveAs MyFileName$ & ".xls"
Else
ActiveWorkbook.SaveAs MyFileName$ & ".xls", 56 '56 = xlExcel8 in Excel 12: versions of Excel prev to 12 do not understand the xlExcel8 constant
End If

 

[LisaBee]

Excellent -- already updated my macros -- can't wait to try it out on Monday!

Thanks very, very much!

And Roy, very informative! Tx!