Mar 8 2012, 02:37 PM
Ok, I'm in the painful process of migrating stuff to Office 2007. There are still a lot of users that need the .xls 2003 version of my file though.
In one of my MS Access projects I am opening/updating/saving a .xlsm file, (previously converted from an '03 Excel version).
I am also using the following line to save the file as a .xls file for those still on '03 which clearly does not work as I'd hoped. Although it appears to save without error as .xls the file cannot be opened in '03
(Note I need to use 'saveCopyAs' b/c my code reverts back to the original file for other purposes.)
Could someone please let me know how to properly save an .xlsm file as an '03 version .xls?
Mar 8 2012, 02:56 PM
xlBook.SaveAs FileName:="U:\Mydirectory\Myspreadsheet.xls", FileFormat:=xlExcel9795
Mar 8 2012, 03:55 PM
I find Chip Pearson's page to be very good at explaining precisely this issue, and I refer to it frequently for this issue:
I'm assuming you have a reference to the Excel vba library in your Access project, so basically it's Excel vba:
For some reason I can't find his page anymore!! (anyone else have that problem?)- I mean the page specifically about ThisWorkbook.SaveAs and FileFormats - but here is what I do:
[workbook object].SaveAs Filename="Your Filename.YourExtension", FileFormat:=51
51 is for excel 2007 format (.xlsx), or like Doug said, and you can also use 43 for his format.
Mar 8 2012, 04:24 PM
I tried adding the fileformat piece next to my code but I guess it does not work with "SaveCopyAs" for some reason.
This produces an app or obj defined error message.
xlBook.SaveCopyAs FileName:="U:\Cons_Pricing_Execution\RATES\Current_Rates_XLS\Employee.xls", FileFormat:=xlExcel9795
I need to use "SaveCopyAs" and not SaveAs because Save as makes that file the current active workbook.
Mar 8 2012, 04:47 PM
Don't use SaveCopyAs, just use it the way Doug put it. Workbook.SaveAs.......
Mar 8 2012, 04:50 PM
Sorry, just saw your last sentence. I still wouldn't use SaveCopyAs.
Try something like:
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Add
For Each ws In ThisWorkbook.Worksheets
wb.SaveAs 'continue with code Doug or I provided
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here