How to avoid “Numbers stored as text” when exporti

Question

When I export a table from Access to Excel, all the numbers show up as text, and have that green arrow on the cell. Is there a way to automatically fix this error, or another way to export that works around the error?

For reference, this is how I export:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, pages(i), outputFileName, True

Answer 1

In Excel you can do this by using the Text to Columns feature of the ribbon.

Highlight the entire block of text you want to be numbers Alt+A, then E, then Alt+F, you're done.

Im not sure if you want an excel or vba way to do this, or do you want ms-access to do something like this before it goes into Excel?

Answer 2

Check your table definition. The only time I get a "numbers stored as text" flag when exporting an Access table to Excel is when the table's column is defined as Text and it contains (something that Excel has decided is) a number, In that case Excel is correct: It is a "number" that is formatted as text because it is text (the table definition said so).

If the column really should be a Number column in the Access table then change it. Alternatively, you can go into File > Options > Formulas in Excel and disable "Numbers formatted as text or preceded by and apostrophe" under "Error checking rules".

Answer 3

Using VBA, format the cell range as "General" ...

range("XYZ").NumberFormat = "General"