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
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".
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?
Using VBA, format the cell range as "General" ...
range("XYZ").NumberFormat = "General"
