TransferSpreadsheet puts ' in cells

[Crystal Powell]

I am haveing a serious problem using transferSpreadsheet
in Access 2002 every time i use it to export to excel it
puts ' in the cells in excel. No other export method does
this but the query has so many records that I can't use
any other export method to dump it. Any suggestions on how
to get rid of the '(single quotes).

 

[Ken Snell]

Are the ' characters at the beginning of all-numeric values that are in text
format? If so, this is just how EXCEL "knows" that the number is not a
number but is text. It shouldn't affect your work at all.

 

[Crystal]

Thanks for replying, It appears to be in fromat of almost
every field and does not allow to poperly sort or perform
calculations. You can't see the ' mark when looking at the
spreadsheet but if you click on a cell it appears at the
top of the screen cell definition.

 

[John Nurick]

Hi Crystal and Ken,

Usually you can get rid of it with a little VBA.

Put something like this into a module:

Sub FixApostrophes()
Dim C As Range
For Each C In Selection.Cells
C.Formula = C.Formula
Next
End Sub

select the cells in question, and then go to Tools|Macros and run
FixApostrophes.