CODEWITHSUNDEEP
excelms-accessvba
gta0004
gta0004

Reputation: 508

How to avoid "Numbers stored as text" when exporting to Excel

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

Upvotes: 3

Views: 30990

Answers (3)

Steve Beller
Steve Beller

Reputation: 11

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

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

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123779

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".

Upvotes: 5

Scheballs
Scheballs

Reputation: 532

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 or way to do this, or do you want to do something like this before it goes into Excel?

Upvotes: 3

Related Questions