Exporting to Excel - Making graphs

[robert maxim]

I would like to export the data from a query to Excel so that I can produce a graph of the data.
I use the Export command in the File drop-down menu, and it sends the data to Excel.

My Problem is that some data from some of my Queries gets exported OK, but for others the data comes with an apostophe inserted in the cell before the number and I have to manually select and remove the apostrophe from the cells so that I can make a graph.

Why does this happen? How can I export the data without the apostrophe appearing in front of my numbers?

thanks

 

[cstahlberg]

Chekc the table design of the tables you are exporting. I would imagine the fields that are getting the apostrophe are actually being exported as text, becasue they are formatted as text in the table design. You can simply change the format of the cell in the table design view from text to the appropriate number format. I woud recommend making a backup copy of the table before doing this however. Then the export should be relatively straightforward.

 

[Kenny]

I know this was a while ago but I have just been having the same issue. The problem was fixed by using the 'OutputTo' command instead of 'TransferSpreadsheet', no apostophes appear in the text cells.

Hope this helps someone as I have been looking at this for 2 or 3 days with much frustration.

CODE:

Private Sub comExport_Click()
Dim file As String

file = "j:\everyone\Vad\Bloomberg\" & Form_fAuditSelect.ReconciliationID & "FIA.xls"

'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tBloomberg", file, True

DoCmd.OutputTo acOutputTable, "tBloomberg", acSpreadsheetTypeExcel9, file, False

End Sub