Excel 2007 with macros and forms fails to save as 2003 1

[CindyK]

An excel template file created in version 2003 has forms and macros in it. The file was distributed to users with 2003 and 2007. We need to make sure the 2007 users do a file, save as to the earlier version because Microsoft's converter fails on files with forms/macros in it (and the person who ultimately gets these files does not have version 2007).

If the user does the file, save as, it works fine. If I try to save the file using code I get an Error 1004 on the save as message.

Here's my code (stripped variable dec and error handling)

sRecommend = "Counseling"
If val(Application.version) < 12 then
'user is in Excel 97 through 2003
FileExtStr = ".xls"
FileformatNum = -4143
Else
'user is in 2007 or 2010 - need to save without the macros
FileExtStr = ".xls"
FileFormatNum = 56
End if

sPath = Application.GetSaveAsFileName(InitialFileRecommend:=sRecommend,_
FileFilter:="Excel Files(*.xls),".xls")

sFile = sPath

Activeworkbook.SaveAs Filename:=sFile, _
FileFormat:=FileFormatNum, _
Password="",WriteResPassword:="",ReadOnlyRecommended:=False, _
Create Backup:=False

End if

The code fails on the last statement - error 1004. Any thoughts?

Thanks in advance.

 

[KiaruB]

u don't need code to do this

it is an option in wich version excel is saving by default

look it up in excel options clicking on the office button in your excel workbook

 

[CindyK]

Hi Kiaru - I realize I don't need code to do it, but the people using this spreadsheet are using it from a variety of PC's in different locations (they download it from the web and never know what the setup will be on the PC they're working from). for that reason, we can't change the default save options, and IF they forget to save it as an earlier version, their home office can't do anything with the file at all. (Remember that we've already tested Microsoft's converter on this file - it not only fails, but it corrupts the file in the process.) SO - the decision was made to have users click a button right on the spreadsheet to do their save.

 

[bubba100]

Using the macro recorder on my machine with Excel 2003:

ActiveWorkbook.SaveAs Filename:="C:\My Documents\Book1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

On my other machine with Excel 2007 set to default to 2003 format:

ActiveWorkbook.SaveAs Filename:="C:\My Documents\Book1.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

you might be better off using if application.version =11.0 then (use the first) else (use the second). Hope this helps.

 

[CindyK]

Really odd...
the constant xlExcel8 won't compile, but using a combination of the constant you got with the macro recorder and the 56 now saves without an error!!!

Thanks for the help. Makes no sense to me at all that this works and the other doesn't, but then.... I guess life would be boring if everything made sense.

Code I ended up with:
'user could have anything from 2000 through 2007
If Val(Application.Version) < 12 Then
'user is in Excel 97 through 2003 - no problemo
ActiveWorkbook.SaveAs Filename:=sFile, _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
ActiveWorkbook.SaveAs Filename:=sFile, _
FileFormat:=56, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


End If