TransferSpreadsheet - Unique Requirement

[ria.arora]

Dear All,

I'm creating new workbook and worksheet through ACCESS VBA (MS Access 2007). After creating new worksheet I'm extracting data from ACCESS and writing into the sheet.
After that I'm creating new sheet and trying to transfer ACCESS query resultset to new created sheet. I'm stuck over here. Cause I have not saved workbook and I need "Full Name of Excel WorkBook" for TransferSpreadsheet. Any idea how to achieve this using TransferSpreadsheet and without saving and opening the file again.

DoCmd.TransferSpreadsheet {Direction of Transfer}, _
{SpreadSheet Type}, {Query to Select Data}, _
{Full Name of Excel WorkBook, True, {Name of WorkSheet}

.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTempQueryName, sWorkbookPath, True, sWorksheetName

 

[Galaxiom]

Use a different method from TransferSpreadsheet

Create a recordset with the information you want to transfer.
Then use the CopyFromRecordset method.

objSheet.Cells(x, y).CopyFromRecordset rsTransfer

 

[Trevor G]

Perhaps it would help if you show the complete code.

 

[boblarson]

Use a different method from TransferSpreadsheet

Create a recordset with the information you want to transfer.
Then use the CopyFromRecordset method.

objSheet.Cells(x, y).CopyFromRecordset rsTransfer

I concur. No need to go through all of that hassle to export