Access 2003 TransferSpreadsheet adds a quote mark to each text field

[TylerTexas]

Doing this in Access 2003


DoCmd.TransferSpreadsheet acExport, , "BuyAKit", efn, True

Exports data fine to Excel 2003
but each text field is prepended with an '
This ' is not in the table fields at all, so it must be part of the transfer method.

Any simple workarounds?

[June7]

These fields are defined as text datatype? Do these fields contain only number characters, as in zip code or SSN?

[TylerTexas]

Yes, defined as text. And SOME of the fields do have only numbers in them, but ALL text fields, regardless of content begin with '

[June7]

Are you exporting a query or table? If query, does this happen with the same fields if export table?

Want to provide db for analysis? Follow instructions at bottom of my post.

[TylerTexas]

I build the table in VBA from a recordsource that is filled by an SQL query.
The table is built with this code in a do loop:

rsb.AddNew
rsb!Item = rsa!Item
rsb!Description = rsa!Description
rsb!Need = rsa!Qty * AssyQty 'Multiply qty by assemblies required
rsb!Vendor = rsa!Vendor
rsb!Cost = rsa!Cost
rsb.Update
BuyAKitAddAssy = BuyAKitAddAssy + 1
rsa.MoveNext

I can look at the table after it's built and there is no '
But when I export to excel, the fields ITEM, DESCRIPTION, VENDOR all start with '
They are the text fields.
The NEED and COST fields are numeric and don't start with '

[JoeM]

I have seem some odd things when working directly between Excel and Access (sometimes they talk to each other and assume too much!).

Out of curiosity, does it happens if you export to Excel using the OutputTo action instead of the TransferSpreadsheet action, i.e.

    DoCmd.OutputTo acTable, "BuyAKit", "MicrosoftExcelBiff8(*.xls)", efn, False, "", 0

Also, out of curiosity, what is the value of "efn"? I wonder if you might get odd results if you choose a file name that have an extension that is not a valid Excel extension.

[TylerTexas]

OutputTo method DOES WORK.

efn = "QBX_BuyAKit.xls"

And I was starting Excel with:
Call Shell("excel.exe " & efn, vbNormalFocus)

Which I now don't have to do by setting Autostart TRUE in the OutputTo method.

Still curious. Noone else ever seen this?
I would think it could be duplicated by simply using my export command in 2003
Like I said, viewing the table seems fine.
Maybe has to do with how I populate the records manually

[June7]

Yes, I have seen this. As JoeM says, communication between apps not perfect.

[JoeM]

Yes, I have seen this. As JoeM says, communication between apps not perfect.

I am convinced that Microsoft sometimes goes overboard and "outthinks itself".

In any event, I'm glad to see it looks like you got a workable solution!

[TylerTexas]

Thanks so much for your help Joe.
I love simple solutions.
This one has my curiosity piqued but I will resist spending a day figuring out which conditions are the culprit.
It does frustrate me that a random newbie like myself seems to find all this weirdness
Thanks again.

[JoeM]

Your welcome.

The reason I was quick to recommend using OutputTo in lieu of TransferSpreadsheet is because I have experienced issues transferring data between Excel and Access also, and if one doesn't work well, I learned to try the other. I gave up trying to figure out why long ago. It might not even be something on the Access side, it could be something on the Excel side of the communication. Without knowing how Micrsoft handles that communication between the two behind the sides, it can sometimes be next to impossible to figure out why it is doing that.

The one that used to frustrate me to no end is importing Excel files into existing Access tables. Often times, they will error out and not import anything, but it gives you absolutely no indication of which fields or records may be the culprit, just a generic "Import Failed" message. Its about as useful as getting the old "General Default" error from the years ago...