export to Excel

[David]

I am trying to export a query to Excel. The preferred method in this user
group seems to be transfer spreadsheet option. However I am getting an
apostrophe at the beginning of each cell. When using the output to option,
exports fine. However I want the ability to name the tab in Excel.
How can I modify the transfer spreadsheet command so it doesn't have an
apostrophe?


900 strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
910 strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:="Please select an export file...", _
Flags:=ahtOFN_HIDEREADONLY)

920 Me.txt_InputFile = strInputFileName

'930 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_tbl_TD_Upload_Export", Me.txt_InputFile, yes, "data export"

930 DoCmd.OutputTo acOutputQuery, "qry_tbl_TD_Upload_Export",
acFormatXLS, Me.txt_InputFile

 

[Ken Snell \(MVP\)]

The apostrophe in the cell is because the field from which those values come
is a text or memo field, so EXCEL puts the apostrophe at the beginning of
the value to show that the "numbers" are actually text and not numeric. Only
way to avoid this is to change that field to a numeric value (use a
calculated field to convert from text to number). However, EXCEL ignores the
apostrophe and its presence should not cause you any problems.

See Alex Dybenko's recent blog for some info about how to use
TransferSpreadsheet for naming a spreadsheet page:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html