Solved

How to Export Excel 2003 xls spreadsheet from Access 2010

Posted on 2014-10-16
8
225 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

706 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

20 Experts available now in Live!

Get 1:1 Help Now