Solved

How to Export Excel 2003 xls spreadsheet from Access 2010

Posted on 2014-10-16
8
230 Views
Last Modified: 2014-11-05
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
Comment
Question by:mlcktmguy
  • 4
  • 4
8 Comments
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40385287
Hi,
Use the follwing command for Save As:

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

-FA
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40385400
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
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40385426
Hi,

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

must be worked.

-FA
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40385441
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 14

Accepted Solution

by:
Farzad Akbarnejad earned 500 total points
ID: 40385472
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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40385969
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
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 40386011
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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40392697
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now