Export data from Access to Excel without losing leading zeroes

Question

I have a table in Access I am exporting to Excel, and I am using VBA code for the export (because I actually create a separate Excel file every time the client_id changes which creates 150 files). Unfortunately I lose the leading zeroes when I do this using DoCmd.TransferSpreadsheet. I was able to resolve this by looping through the records and writing each cell one at a time (and formatting the cell before I write to it). Unfortunately that leads to 8 hours of run time. Using DoCmd.TransferSpreadsheet, it runs in an hour (but then I lose the leading zeroes). Is there any way at all to tell Excel to just treat every cell as text when using the TransferSpreadsheet command? Can anybody think of another way to do this that won't take 8 hours? Thanks!

Answer 1

What if you:

1. In your source table, change the column type to string.
2. Loop through your source table and add an "x" to the field.

If the Excel data is meant to be read by a human being, you can get creative, like hiding your data column, and adding a 'display' column that references the data column, but removes the "x".

Answer 2

As an alternative to my other suggestion, have you played with Excel's Import External Data command? Using Access VBA, you can loop through your clients, open a template Excel file, import the data (i.e. pull instead of push) with your client as a criteria, and save it with a unique name for each client.

Answer 3

prefix the Excel value with an apostrophe (') character. The apostrophe tells Excel to treat the data as "text".

As in;

0001 'Excel treats as number and strips leading zeros

becomes

'0001 'Excel treats as text

You will probably need to create an expression field to prefix the field with the apostrophe, as in;

SELECT "'" & [FIELD] FROM [TABLE]