Change docmd from Export to paste into specified range
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(FileP
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")
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(FileP
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")
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Take a look at the Excel CopyFromRecordset method
Syntax looks like:
sht.Range("A2").CopyFromRe
Where rs would be defined by either a table or a query,
Syntax looks like:
sht.Range("A2").CopyFromRe
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
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(FileP
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
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
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(FileP
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_r
xlSheet.Range("B10").CopyF
rs.close
set xlSheet = xlBook.Worksheets(2)
set rs = Currentdb.querydefs("qry_r
xlSheet.Range("B10").CopyF
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.
set xlSheet = xlBook.Worksheets(1)
set rs = Currentdb.querydefs("qry_r
xlSheet.Range("B10").CopyF
rs.close
set xlSheet = xlBook.Worksheets(2)
set rs = Currentdb.querydefs("qry_r
xlSheet.Range("B10").CopyF
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.
Dim rs as DAO.Recordset
set xlSheet = xlBook.Worksheets(1)
set rs = Currentdb.Openrecordset(Ta
xlSheet.Range("B10").CopyF
rs.close
set xlSheet = xlBook.Worksheets(1)
set rs = Currentdb.Openrecordset(Ta
xlSheet.Range("B10").CopyF
rs.close
Premium Content
You need an Expert Office subscription to comment.Start Free Trial