shawthingz
New Member
- Joined
- Aug 19, 2006
- Messages
- 49
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
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
