Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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.
0
mlcktmguy
Asked:
mlcktmguy
  • 4
  • 4
1 Solution
 
Farzad AkbarnejadCommented:
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 AkbarnejadCommented:
Hi,

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

must be worked.

-FA
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 AkbarnejadCommented:
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
 
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 AkbarnejadCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now