Transferspreadsheet glitch

  • Thread starter Thread starter Ben Hudgins
  • Start date Start date
B

Ben Hudgins

I'm getting a funky error when I run the following:

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "MyQuery", "C:\Test.xls", True

When I open "Test.xls", all of the string fields have been
prepended with an apostrophe (ie. LastName has
become 'LastName). Has anyone else encountered this little
baby or am I simply delusional?

Thanks in advance,
Ben
 
No, that simply means that ACCESS added the ' in order to tell EXCEL that
the value is a text format, not a number format.

The apostrophe will have no impact on the actual data in the EXCEL file.
 
Thanks for in the info, Ken. I'm aware of what Access is
doing, but is there any way to keep it from doing it?

Reason is this: I have a routine in Access that builds
pivot tables. When the Access routine tries to grab the
fields from the data range, it chokes because instead of
seeing Field1, it sees 'Field1. I've considered a replace
command, but that's none too elegant.

Is there a toggle or somesuch that will keep Access from
prepending the apostrophe? After all, this addition is a
new "feature" in Access 2002 (amongst a host of similarly
useless and annoying changes). Access97 (where I orignally
developed the routine) doesn't do it.

Cheers again,
Ben
-----Original Message-----
Click to expand...
No, that simply means that ACCESS added the ' in order to
tell EXCEL that the value is a text format, not a number
format.

The apostrophe will have no impact on the actual data in
the EXCEL file.

Ken Snell
 
Hmmm.... well, I'm not sure that you can "turn that off" when you're
exporting numbers as text using the TransferSpreadsheet command. I will do
some checking on this and post back.

Alternatively, you could use VBA code that would write directly into EXCEL
using Automation. That would allow you to bypass the ' character because you
would control what you write into the cells.
 
OK - short answer is that you cannot turn them off.

You can eliminate them in the EXCEL spreadsheet after you've done the import
(using Automation) by one of the following methods (my thanks to John
Nurick, ACCESS MVP, for his assistance):

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

(2)
Put a 1 in an empty cell, copy that cell, select the cells that need to lose
the apostrophe, and do a Paste Special Multiply. This operation could be
written into VBA code by choosing a cell not being used on the active
worksheet, or by adding a new worksheet, using a cell on that worksheet, and
then deleting the new worksheet.


Alternatively, it appears that you could get the data from ACCESS by using
EXCEL's Range.CopyFromRecordset method. That apparently imports
text-formatted numbers without the apostrophe and without formatting the
cells as text.

Hope that one of these will work for you.
 
Ben -

By any chance are you using the Format function to "format" the numbers in
the query that you're exporting?
 
Nope. It's pretty much a straight SELECT query dumped to
Excel.

BTW, thanks for all the other info you posted prior to
this. I'll be mucking about with my code today and
incorporating the stuff you suggested.

Cheers,
Ben
-----Original Message-----
Click to expand...
Ben -
By any chance are you using the Format function
to "format" the numbers in the query that you're exporting?
 
Back
Top