URGENT HELP! Export to Excel Template multiple cells

[sunilkeswani]

Hi,

I would appreciate any help I can get with this. I have a command
button in Access through which more than once queries & tables would
get exported to a specific excel template. I am only able to create a
new worksheet, but I dont know how to paste a particular query's data
into a specific worksheet.

This is the code.

Private Sub Command0_Click()


DoCmd.TransferSpreadsheet acExport, 8, "UsysETDBEscalationsToday",
"C:\Temp.xls", True


Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open "C:\TEMPLATE.xls"
xlObj.activeworkbook.saveas "C:\Analysis.xls"
xlObj.Workbooks.Open "C:\Temp.xls"
xlObj.activesheet.cells.select
xlObj.activesheet.cells.Copy
xlObj.Workbooks("Analysis.xls").Activate
xlObj.activeworkbook.sheets(1).range("a1").select
xlObj.activesheet.paste
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Set xlObj = Nothing


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"OthersToday", "C:/Analysis.xls", , "OTHERDUMP"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ETDBMTD",
"C:/Analysis.xls", , "MTD DUMP"

MsgBox "MIS Report exported successfully"

DoCmd.RunMacro "Macro1"



Please advise on how I can paste "OTHERDUMP", not as a new sheet, but
just paste the data into an existing sheet called "OTHERDUMP"


Regards
Sunil

 

[Mauricio Silva]

Hi,

it looks to me you could use the command CopyFromRecordSet

Syntax: expression.CopyFromRecordset(Data, MaxRows, MaxColumns)

Example:
set rst = currentdb.OpenRecordSet( YourSQLHere, dbopensnapshot)
xlObj.activeworkbook.sheets(1).Cells(1,1).CopyFromRecordset rst

Let us know

Mauricio Silva