Exporting Access Data into Excel - Formatting Issues

[dbuuch]

I have used both the DoCmd.OutputTo and the DoCmd.TransferSpreadsheet methods to export data from an Access DB into Excel.

the following is an example of the code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sQueryName, strPath

The problem i have is that, in Excel, all cell contents of the exported data are prefixed with a single open quote mark. this makes numbers and Dates appear as Text and so do not allow for any mathematical operations to be done with the number.

Is there anything i can do to correct this?

 

[brd24gor]

Make sure your fields aren't defined as text in the field definitions of the table(s) you are pulling the data from. TransferSpreadsheet will bring in numbers and dates correctly if they are defined as those datatypes in the Access tables.

--Brad

 

[dbuuch]

Thanks Brad but this doesn't solve the problem. There are other text field that are also prefixed with the single quotes, besides the number and date fields.

when the data is imported using the "import External Data" functionality in Excel, however, there are no single quotes and the data look perfect.

i would still like to use vba to export the data though but would like to get rid of this single quote prefixes.

daniel