TransferSpreadsheet WITH Save Formatted option

[Guest]

I have briefed both DoCmd.TransferSpreadsheet and DoCmd.OutputTo. Both have
their strengths and their purpose.

DoCmd.OutputTo Method is great if one is transferring a single Table or
Query to a spreadsheet. However, I need to export several Tables to different
worksheets in the same Excel file. This is easily done using the
DoCmd.TransferSpreadsheet method. There is, however, one small problem.

It would appear that the DoCmd.TransferSpreadsheet method does not offer the
'Save Formatted' option whereas the DoCmd.OutputTo option does.

Other than using a considerable amount of code to actually open up the Excel
file and adjust the formatting, is there another method/parameter so that the
DoCmd.TransferSpreadsheet method exports the formatting similar to the "Save
Formatted" feature?

Thanks for the help.

Pat

 

[Ken Snell \(MVP\)]

Short answer... no.

If you want to have a formatted EXCEL worksheet when the export is done, you
will need to use Automation after you do the export, or use Automation to do
the export and then the formatting, or export into a workbook that contains
one worksheet (named the same as the query that you're exporting) that is
already formatted fully the way you want the final worksheet to look
(trickier than it sounds, because you must have the same number of rows
already formatted to match the number of rows in the query's output).

--

Ken Snell
<MS ACCESS MVP>

"Pat (Disciple of Aloof-ism)"