Transferspreadsheet produces leading quote in Excel

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

I am using the following statement to export a query from Access 2003
to Excel.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
mstrQueryName, Me.txtExportFile, True, ""

The problem is that every cell in the new spreadsheet has a leading
single quote in it (sort of). I say "sort of" because if I write some
code in the spreadsheet, looking at the value of ever cell for a
leading quote (so I can remove it) NONE of them seem to have one. But
if I click on the cell, I can see it in the formula line and delete
it.

If I use the OutputTo method, I don't get this leading quote, but then
that truncates the memo fields to 255 characters. So that's not really
an option.

So, anyone know why the single quote is appearing with this method and
anyway I can stop it or remove it after the spreadsheet is created?

Thanks,
Larry
 
The leading quote tells EXCEL that the data in the cell are "text" and not
"numeric". When ACCESS exported the data, it "told" EXCEL that it was
exporting "text" values, hence the ' is inserted at the beginning of the
cell.

The ' character will not interfere with your data in EXCEL; best if you
leave it there so that EXCEL will know how to handle the data.
 
The leading quote tells EXCEL that the data in the cell are "text" and not
"numeric". When ACCESS exported the data, it "told" EXCEL that it was
exporting "text" values, hence the ' is inserted at the beginning of the
cell.

The ' character will not interfere with your data in EXCEL; best if you
leave it there so that EXCEL will know how to handle the data.

--

Ken Snell
<MS ACCESS MVP>











- Show quoted text -
Click to expand...

I guess part of my question (not reall important but it peaks my
technical curiosity) is how does the quote show up when I am on the
cell, and I can delete it; yet not show up via code so I could delete
it that way?

I know the users will question this quote, as soon as they notice it.
It's a bunch of lawyers and if they see an extra character in their
legalize when exporting to Excel, they will certainly ask questions!
 
EXCEL does not display the quote unless you click on the cell and view the
data in the Formula bar, or double-click into the cell and see the data in
the cell. The quote is a "partially hidden" way for EXCEL to tell the
difference between text and general format; especially needed for numbers
that are to be treated as text and not as numeric data.

The quote is not part of the export from ACCESS per se; you won't find it in
the ACCESS data prior to export. It is added by EXCEL after the data are
"received".

The only way to eliminate it is to not export the data from ACCESS as text
data. And that may not be possible, depending upon what you're exporting
(query or table) and what the data are that are being exported.
 
This may not help, but I just copied the Access cells to the clip board, then pasted them into Excel (I got a box asking if it was Unicode, which was good) and that gave me the Access contents without the '.
 
Back
Top