How to Export Excel 2003 xls spreadsheet from Access 2010

I wrote an application for a client several years ago using Access 2003.  One piece of the application generated an output spreadsheet using the code in the code window.

Public Sub ExportFinalSpreadSheet(returnNameAndPath As String)
Dim rs As DAO.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim olApp As Object
Dim olMsg As Object
Dim filepath As String
Dim filedate As String
Dim spreadName As String
'
Dim wkQuery As String
'
Dim wkClientString As String
'
Dim wkInconsistentCaption1 As String
Dim wkInconsistentCaption2 As String
'
Dim wkAbnormalCaption1 As String
Dim wkAbnormalCaption2 As String
'
'Dim wkCellI1_Caption As String
Dim wkOutputFolder As String
'Dim wkRegion As Long
Dim wkWhere As String
'
'wkClientString = "Results For Client - " & Trim(Str(passedClientID)) & ", " & getClientNameFromClientNum(passedClientID)
wkClientString = " "
'
wkInconsistentCaption1 = gSpreadClientOrGroup & "Pain Management Utilization "
wkInconsistentCaption2 = "For The Period " & CDate(gRepStartDate) & " To " & CDate(gRepEndDate)
'
wkAbnormalCaption1 = "Abnormal " & wkClientString
wkAbnormalCaption2 = wkInconsistentCaption2
'
'spreadName = "Client_" & Str(Trim(passedClientID)) & "_Alert_" & _
'                CDate(gRepStartDate) & "_To_" & CDate(gRepEndDate)
'
spreadName = wkInconsistentCaption1 & _
                CDate(gRepStartDate) & "_To_" & CDate(gRepEndDate)
'
spreadName = Replace(spreadName, "/", "_")
'
'BuildSpreadSheetWkTable eResultType.eInconsistentResult  
BuildSpreadSheetWkTable eResultType.eAllResults
 
'selectClient = "Select * From qryData_ToExportFrom_wkTbl Where [ClientID] = " & passedClientID
'
' Geoff also wants all clients on one sheet
'
selectClient = "Select * From qryData_ToExportFrom_wkTbl " ' Where [ClientID] = " & passedClientID
'
'debug.print "Select String: "; selectstring
'
DoCmd.Hourglass True
'
' Create spreadsheet from template
'
Set xlApp = CreateObject("Excel.Application")
 
xlApp.DisplayAlerts = False
'
Set xlWb = xlApp.Workbooks.Add("C:\Access\PainMed.xlt")
'
' Inconsistent is on first tab  'per Geoff, inconsistent and abnormal go on the same tab.
'
'selectResultCommmentTypeID = " And [ResultCommentsTypeID] = " & eResultType.eInconsistentResult
selectResultCommmentTypeID = " "
'
selectString = selectClient & selectResultCommmentTypeID
'
'Debug.Print selectString
Set rs = CurrentDb.OpenRecordset(selectString)
xlWb.Worksheets(eExcelTab.eInconsistentTab).[a1] = wkInconsistentCaption1
xlWb.Worksheets(eExcelTab.eInconsistentTab).[a2] = wkInconsistentCaption2
xlWb.Worksheets(eExcelTab.eInconsistentTab).[a4].Copyfromrecordset rs
'
'
' Create Final spreadsheet
'
wkOutputFolder = cDefaultSpreadLoc    'selectedOutputFolder()
'
filepath = wkOutputFolder & spreadName & ".xls"    'gSelected can be set in other routines
'
returnNameAndPath = filepath
'
xlWb.saveas filepath
xlWb.Close
'
Set xlWb = Nothing
Set rs = Nothing
'
'MsgBox "Spreadsheet " & filepath & " has been created"
'
End Sub

Open in new window


It worked well for years.  Recently the client has started to make a transition to Access 2010.  When we run the included code on a machine running Access 2010a spread sheet is generated with the .xls extension but it doesn't seem to truly be an xls/Excel 2003 version.

Users running Excel 2003 cannot open the spreadsheet.  

Users running Excel 2010 get a message when the try to open the spreadsheet
"the file you are trying to open xxxx.xls is in a different format than specified by the file extension.  Please verify that is not corrupted and from a trusted source."  The can then open the file and view it perfectly.

The above code is definitely putting an ".xls" extension on the spreadsheet generated but my guess is that the actual spreadsheet is in xlsx (2010) format.

How can I get this spreadsheet to generate in 2003 format?

I'm thinking one option would be to generate the spreadsheet with an xlsx extension and make sure the users have the compatability pack from MS but this spread is sent ot external cusotmers and we have so say or authority to have them do that.
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Farzad AkbarnejadDeveloperCommented:
Hi,
Use the follwing command for Save As:

    xlWb.SaveAs Filename:=filepath, FileFormat:=xlExcel8

-FA
0
mlcktmguyAuthor Commented:
Thank you.  I am getting a compile n error on 'xlExcel8', Variable Not found.

I am compiling in Access 2003 in case that makes a difference.  It should work in 2003 since some of the people that execute this have not been updated to 2010 yet.
0
Farzad AkbarnejadDeveloperCommented:
Hi,

 xlWb.SaveAs Filename:=filepath, FileFormat:=56

must be worked.

-FA
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mlcktmguyAuthor Commented:
It compiles cleanly but on execution I'm getting

Run time error 1004
Saveas method of Workbook class failed

This is my exact code:
xlWb.SaveAs FileName:=filepath, FileFormat:=56
0
Farzad AkbarnejadDeveloperCommented:
I think that because of file lock on your target (filepath) you couldn't save as on it again. Change you path or restart you access and run program again.

-FA
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
That's not the issue because I can run the procedure over and over again by commenting the new statement

 xlWb.SaveAs Filename:=filepath, FileFormat:=56

and generating the spreadsheet with the old statement

xlWb.saveas filepath

I can generate several spread sheets, one after the other without ever leaving Access.
0
Farzad AkbarnejadDeveloperCommented:
And last try:
Make sure that your Access file save in 2010 format.
If it doesn't work I don't know any more idea.

-FA
0
mlcktmguyAuthor Commented:
I verified that the documents referenced in the original question are fro sure being generated in the 2007 format.  They only have an xls extension because I build the file name in the code.

When I changed the extension to the xlsx, they opened right up in 2010 and also opened in the 2003 version if the user had a conversion pack.

Any other suggestions would be greatly appreciated
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.