Alternative to TransferSpreadsheet

[Jeff C]

I have a function using TransferSpreadsheet that loops through a recordset
creating a number of workbooks. I added excel automation before the loop
that formats the data in the workbook.

I was hoping to use the SendObject method to email the final product but
since the Excel formatting occurs after the TransferSpreadsheet method runs
I've no way to send the final Excel workbook.

Is there an alternative to TransferSpreadsheet that will let me place the
query results into a Workbook, format the data and then result in a final
output I can use in with SendObject as well as saving to a file?

Thanks in advance.

 

[Klatuu]

I don't understand why not.
If you use the TransferSpreadsheet to put the data in the file, then use
Automation to format and save the fie, why can't you just use the SendObject
after that to send it?

Actually, if you are using automation already, you might consider putting
the data in the spreadsheet during the automation using the copyfromrecordset
method.

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

I don't understand why not.

The transferspreadsheet method is saving the query results as a file and
then I Create the Excel Application Object to work on it.

I don't know how to reference the Excel Object after it is reformatted.

If you use the TransferSpreadsheet to put the data in the file, then use
Automation to format and save the fie, why can't you just use the SendObject
after that to send it?

I thought the SendObject required that I name one of the objects in the
Acess database ie, query, report, table - how do I reference the workbook
file after it is formatted?

Actually, if you are using automation already, you might consider putting
the data in the spreadsheet during the automation using the copyfromrecordset
method.

I'm not sure how to do that, too new at all this stuff, willing to learn.

Thanks for responding, many of your solutions have helped me before.