Warning when opening xls file saved in compatibility mode

[shawthingz]

Hi Folks,

Hope this posting isn't too long - I'm trying to provide you with as much info as possible to help me to fix this...

I'm *hoping* that someone out there (with more Wisdom than me) can help - I work in a company where people in various offices around the world are using different versions of Excel (I'm one of the 'fortunate' ones having previously used 2007 & now have 2010! )

Anyhow, this means that in the macros I'm using in a particular Workbook, I now need to save a specific Worksheet in Excel 2003-2007 'compatibility mode'. Thanks to all the great help out there, I'm currently using the following code snip to generate this file (with a password as we're ISO27001 compliant). & then save it to the User's Desktop:

---

DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
FileName = "FileNameWithPassword.xls"

FullyQualifiedFileName = DTAddress & FileName

ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, FileFormat:=xlExcel12, Password:="password" (Password value changed for security reasons )

---



This all seems to work fine & when I check the saved file properties, I'm told (by the Windows 2007 OS) that the file type is "Microsoft Excel 97-2003 Worksheet (.xls)"

However, when I open the new file & enter the appropriate password, I get a warning message stating:

"The file you are trying to open, 'FileNameWithPassword.xls', is in a different format than specified by the extension...Do you want to open the file now?"

If I change the file format to be CSV (& remove the password as I've seen they're not supported), everything works fine.

Any thoughts / comments / ideas on why this may be happening & how to fix it would be much appreciated.

Thx

shawthingz

 

[xenou]

ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, _
FileFormat:=[COLOR="Red"]xlExcel12[/COLOR], _
Password:="password" (Password value changed for security reasons )

You are saving in Excel 12 file format - that's XL2007. You want the Excel 2003 file format, I think:

Try instead:

ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, _
FileFormat:=xlExcel8, _
Password:="password" (Password value changed for security reasons )

Or:

ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, _
FileFormat:=56, _
Password:="password" (Password value changed for security reasons )

ξ
More info on saving files:
http://www.rondebruin.nl/saveas.htm

 

[shawthingz]

Works perfectly - many thanks!

shawthingz