Export to Pocket Excel

[Sandie]

Hi,

I am exporting some data from an Access 2002 query to an Excel spreadsheet
for use on a mobile device (using MS ActiveSync 4.5.0).

Using the transferspreadsheet method, I successfully export the data and it
syncs to the mobile just fine however, when I try to actually open the
spreadsheet on the mobile, it says the file is an unrecognized format (.xls).

However, if I launch the spreadsheet just once and click save (not save as,
and without even making any changes) prior to the sync, it will open just
fine on the mobile. It's almost like after the export the excel spreadsheets
need to be "initialized" once before the mobile will recognize it. If I
create a new spreadsheet on the PC and synch it, it opens without incident.

So I've tried several things in my code: launching excel and the
spreadsheet and closing it (no good); launching the spreadsheet and forcing a
save (can't get this code to work) and creating a blank spreadsheet
"template" to export the data to (so that I have a working instance of an
excel spreadsheet already created) and this particular code crashes Access:

Any advice is greatly appreciated!!

Dim obExcel as Excel.application
Dim objWorkbook as Excel.workbook
Dim strFileName as string

strFileName = "C:\Book1.xls" (pre-made blank spreadsheet)

set obExcel = CreateObject("Excel.Application")
obExcel.visible = true [had this as false at one point]
Set objWorkbook = obExcel.Workbooks.Open(strFilename)
Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryExport",
strFilename

objWorkBook.Close True (trying to save here)
Set objWorkbook = nothing
set obExcel = nothing

 

[Ken Snell [MVP]]

Try this:

Dim obExcel as Excel.application
Dim objWorkbook as Excel.workbook
Dim strFileName as string
strFileName = "C:\Book1.xls" (pre-made blank spreadsheet)
set obExcel = CreateObject("Excel.Application")
Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"qryExport", strFilename
Set objWorkbook = obExcel.Workbooks.Open(strFilename)
DoEvents
objWorkBook.Close True (trying to save here)
Set objWorkbook = nothing
obExcel.Quit
set obExcel = nothing