VBA 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?

 

[ryguy7272]

Is that a new instance of Excel, or are you sending this data to an Excel
template that is used over and over again? One thing you may consider is
just leave things as they are, and make the final step a format-operation of
Excel. You can easily control Excel, from Access; don't even have to leave
the comfort of MS Access environment.

Look at this link on my site for info:
http://www.consulting-group360.com/Code.aspx

It's the first VBA sample.

Good luck!!

 

[mcescher]

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 confusingis
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 isa
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?

One thing to check is the formatting in the template file. If it's
formatted goofy in there, that can affect your final formatting. If
your template file is formatted correctly, you shouldn't have to do
any from Access.

HTH,
Chris M.