Change docmd from Export to paste into specified range

Take a look at the Excel CopyFromRecordset method

Syntax looks like:

sht.Range("A2").CopyFromRecordset rs

Where rs would be defined by either a table or a query,

hi fyed

thanks for the feddback,,, But i am not sure how to complete task....here is the full code..

DoCmd.Echo True, "Exporting, Please Wait.........."

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim RootMIdir As String
Dim FilePath As String

RootMIdir = "\\C:\Output Files\Report Templates"

FilePath = RootMIdir & "\RC_W_REPORT_SLA_Template" & Format(Now(), " dd-mmm-yy") & ".xls"

DoCmd.TransferSpreadsheet acExport, 8, "Report1_BANANA", FilePath, True, "BANANA"
DoCmd.TransferSpreadsheet acExport, 8, "Report2_GRAPE", FilePath, True, "GRAPE"
DoCmd.TransferSpreadsheet acExport, 8, "Report3_ORANGE", FilePath, True, "ORANGE"

Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False

Set xlBook = xlApp.Workbooks.Open(FilePath)
Set xlSheet = xlBook.Worksheets(1)
Set xlRange = xlSheet.Range("b10:g17")
Set xlSheet2 = xlBook.Worksheets(2)
Set xlRange2 = xlSheet.Range("b10:F17")
Set xlSheet3 = xlBook.Worksheets(3)
Set xlRange3 = xlSheet.Range("b10:F17")

DoCmd.Echo True, "Exporting, Please Wait.........."

xlSheet.Activate
xlBook.Save

xlBook.Close
xlApp.Quit

I cannot say that I've ever used the TransferSpreadsheet method to transfer a report to a spreadsheet, so I cannot specifically help with that.  But to post the results of a query or table to Excel, the general syntax would look something like:

set xlSheet = xlBook.Worksheets(1)
set rs = Currentdb.querydefs("qry_rpt_Report1_Banana").Openrecordset
xlSheet.Range("B10").CopyFromRecordset rs
rs.close

set xlSheet = xlBook.Worksheets(2)
set rs = Currentdb.querydefs("qry_rpt_Report2_Grape").Openrecordset
xlSheet.Range("B10").CopyFromRecordset rs
rs.close

But the CopyFromRecordset method does not copy column headers, so you have to do that yourself, and if the report contains headers or groupings, those won't show up either, but I like the flexability.

Hi Fyed

I am pulling the data frOm a table not query...

Also what would I need to dim

Dim rs as DAO.Recordset
set xlSheet = xlBook.Worksheets(1)
set rs = Currentdb.Openrecordset(Tablename)
xlSheet.Range("B10").CopyFromRecordset rs
rs.close

Hi Fyed

Sorry but not working it's saying cannot find table when the table name as per ms access is correct..,

What I am trying to do is copy data from a access table and paste that data
Into ms excel file...rowb10:g17
Thanks

Run time error 3078

Thanks for the feedback.

Glad to help.