DoCmd.TransferSpreadsheet and keeping the same format

[djpingpong]

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.

HERE'S MY QUESTION:
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 as well?

Thanks for the help.

 

[djpingpong]

Oh.. and I forgot to add this important point...

I have multiple tables/queries that I need to transfer to the same excel file... this is why I'm using TransferSpreadsheet

but I need to keep the formatting at the same time

is this even possible in Access?

 

[SkipVought]

Hi,

Queries do not have formatting in Access, do they?

And are you referring to table formatting? Like what? I am not familiar.

Maybe you need to post in one of the many MS Access forums.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[djpingpong]

Hi,

sorry, I did mean to say table formatting...
I'm not having any problems with the queries, its the table formatting that I would like to have transferred as well when its a table