how to save file as 97-2003 xls not xlsx in vba

[Ceri_m]

Hi there, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’ve got a file which from a macro sorts the data then splits it into sections, then creates a new workbook for each section, renames the tabs and and saves the new files as the tab name, for us to email out to different companies. The problem I’ve got, is that the companies that we’re sending to all have different versions of Excel, and we’re getting errors coming back each time as to being able to open the file, or the file being corrupted. What I’d like, is to save the files as 97-2003 xls, so everyone can open them, but no matter what I’ve tried (so far) in the fileformat, it saves as xlsx, which the people with earlier versions of Excel don’t seem to be able to open. I’ve tried adding “.xls” to the end of the file name, but then it comes up with the error ‘the file format is different to the file extension’. For some users this is fine, but again for earlier users, the file corrupts.<o></o>
<o></o>
So far I’ve tried:<o></o>
<o></o>
…Other code…. <o></o>
Dim FileExtStr As String<o></o>
Dim FileFormatNum As Long<o></o>
Dim FileFormatType As String<o></o>
FileExtStr = ".xls": FileFormatNum = 56: FileFormatType = "xlExcel8"<o></o>
<o> </o>
Then further down when it’s created the new workbook<o></o>
With NewWB<o></o>
FileExtStr = ".xls": FileFormatNum = 56: FileFormatType = "xlExcel8"<o></o>
End With<o></o>
Then to save the file <o></o>
<o> </o>
'Save as Tab Name to specified folder<o></o>
ActiveWorkbook.SaveAs Filename:=Range("u1") & Range("u2").Value & sheetName & ".xls"<o></o>
<o></o>
With ActiveWorkbook<o></o>
FileExtStr = ".xls": FileFormatNum = 56: FileFormatType = "xlExcel8"<o></o>
End With<o></o>
<o> </o>
I’ve tried replacing the FileFormatNum with -4143, and 52, and without the FileFormatType, but none of them make any difference. Without the “.xls” bit, it always reverts to saving as an .xlsx file. <o></o>
I’m working in Excel 2010, the file is an xlsm file, but I’ve also tried it with the initial file being a 97-2003 xls file, and I still can’t make it work.<o></o>
<o> </o>
My knowledge of code isn’t very good, Any help would be most appreciated! Thanks!! <o></o>

 

[mgm05267]

Hi...

Only change in your code is to replace

FileExtStr = ".xls": FileFormatNum = 56: FileFormatType = "xlExcel8"<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o></o>with

FileFormat:=xlExcel8

Regards,

MGM

 

[Firefly2012]

Hi

The SaveAs method allows you to specify the FileFormat:

ActiveWorkbook.SaveAs "C:\Somefile.xls", FileFormat:=xlWorkbookNormal

EDIT: MGM's code is the one to use.

 

[Ceri_m]

Brilliant, thank you both! It's npw working!