TransferSpreadSheet - Export to XLS

[nicodemus]

Hi all,

using Win2000, Office2000 (9.0.6926 SP-3).

I was used to use the "OutputTo" method to export my query to Xls and
everthing was fine.
Last days, my query reported more than 16.000 rec so I had to use the
"TransferSpreadSheet" method instead to pass by this limitation :
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Q_charge_Discrepancy", "c:\test.xls", True

The prob I'm facing now is the "text" fields exported to Xls. Excel keeps on
adding a single quote (') in front of each cell containing alphanumeric
values (ie: 'France, 'Germany,...).
Is there a way to prevent "TransferSpreadSheet" to add this single quote ?
Keeping in mind that "OutputTo" doesn't add it !

Thanks a lot in advance for any insight.
Nicodemus

 

[Ken Snell]

The ' is how ACCESS tells EXCEL that the numeric value is text and not
numeric. EXCEL knows how to use and interpret that ', so there should be no
need to delete it after the fact. I don't know of a way to "turn off" this
feature.

However, if you want to do that, you could run Automation code that loops
through the EXCEL cells and deletes the ' from the values (the cells should
be formatted as text, so this should not make EXCEL now think that the
values are numeric).

But this should not be necessary.