Exporting Access Data into Excel - Formatting Issues

[Dire]

I have used DoCmd.TransferSpreadsheet method to export data from an Access DB into Excel.

the following is an example of the code:


CODE:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sQueryName, strPath

The problem i have is that, in Excel, all text field cells are exported prefixed with a single open quote mark.

Is there anything i can do to correct this?

 

[SkipVought]

Hi,

The Single TIC prefix, is the cell designation for TEXT in Excel.

Skip,

_[red][/red]

 

[Dire]

thanks for your reply.

if i don't want the ' (excel's text identifier) to be present in the output file, how can i accomplish that?

reason i ask is that the output file from the db is input for some other application and that application needs that the text fields don't have ' in them.

really appreciate your help.

 

[xlbo]

Tools>Options>Transition

make sure all options are unticked

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Dire]

i checked that in excel and all options are un-ticked in

Tools > Options > Transition

i have tried Transferspreadsheet and OutputTO in Access VBA to export, and both of them are giving the same issue. is there any other function that i could use to export.

thanks.

 

[SkipVought]

Instead of export FROM Access TO Excel...

import in Excel FROM Access using Data/Get External Data/New Database Query...

Skip,

_[red][/red]