We help IT Professionals succeed at work.

Change docmd from Export to paste into specified range

route217
route217 used Ask the Experts™
on
Hi Experts

How would i change the following VBA from exporting the data from an Access table into a new worksheet to pasting the data into the following range as shown below:

DoCmd.TransferSpreadsheet acExport, 8, "Report1", FilePath, True, "A1"
DoCmd.TransferSpreadsheet acExport, 8, "Report2", FilePath, True, "A2"
DoCmd.TransferSpreadsheet acPaste, 8, "Report3", FilePath, True, "A3"


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")
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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,
route217Junior

Author

Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
route217Junior

Author

Commented:
Hi Fyed

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

Also what would I need to dim
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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

Author

Commented:
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
route217Junior

Author

Commented:
Run time error 3078
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Take a look at the attached sample.
CopyFromRs.mdb
route217Junior

Author

Commented:
Thanks for the feedback.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Glad to help.