VBA code push to Excel issue

[DianneF]

I have an MS Access database that outputs multiple tables to multiple
preformatted worksheets on an Excel Template. The code loops through names
and only outputs that person's data then saves the spreadsheet with that
person's name. I am having an issue where the data looses it's format. For
example, currency value formats switch to date formats. What is confusing is
that it does this randomly in the field, not all values get their formats
changed. I have stepped through the code and watched this happen. This is a
headache for the user because this code produces over 100 spreadsheets that
then have to be reformatted. I am using named ranges and .CopyFromRecordset
code.

Help please, does anyone know a fix for this?

 

[Roger Carlson]

I use a slightly different method to export to formatted sheets -- I don't.
What I mean is I export access data to an unformatted sheet and link the
cells from the unformatted sheet into a formated one. I save this as a
template and use it over and over again. This way I can use the simple
TransferSpreadsheet method and still retain formatting.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb" which illustrates how to do this. You can
download it for free here:
http://www.rogersaccesslibrary.com/forum/topic350.html.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L