TransferSpreadsheet question

[Michael]

Hello -

I've got an Access 2003 application that I use to import data and generate
some month end reports. One of the reports needs to be exported into Excel.
I'm using the TransferSpreadsheet method in VBA. My question is, can I
control the format of how the spreadsheet will look? For example, I want a
certain font and size. I'd also like to set specific column headers and a
page header. If possible, I'd even like to add a total line and some lines
around the cells. Right now after I export the report, I still have to
manually make it look the way I want.

Thanks for the info!!
Michael

 

[Pendragon]

Set up a template in Excel which formats your cells as you desire. Set
whatever totals or formulas you want as well.

Then, rather than using the TransferSpreadsheet method, set a recordset to
get your data and utilize DoCmd.CopyFromRecordset. You would need to set
object methods for Excel and your workbook, and then set a reference to the
beginning cell of where you want the information copied.

Do a search on "set Excel reference" to get some help on that, or post back
and I will reply with an example I have used previously (but that will be in
a couple days). Searching on CopyFromRecordset will get results as well.

HTH

 

[Michael]

Thanks so much. That gives me something to go on. I knew there had to be
another method. I'll do a little research and give this a try.