none
Access VBA: Export to specific Excel worksheets RRS feed

  • Question

  • Hi community,

    Let's say, I have 2 queries in Access.

    Query1 name = "qry_A";

    Query2 name = "qry_B"

    I want to write VBA to export to those 2 queries to an existing Excel file (C:\Report\Result.xlsx) has 2 datasheets: 1) TabA; 2) TabB;

    How could I write this VBA code?

    M.

    Tuesday, September 15, 2015 7:52 PM

Answers

  • Hi MelZZ,

    The easy way to export the query to spreadsheet using DoCmd.TransferSpreadsheet method. However, this method wouldn't let us choose the worksheet to export. It would export the data appending last worksheet and name it automatically. We can manipulate the spreadsheet via Excel object model to rename the worksheet and delete the target worksheet.

    Code for export data to spreadsheet:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table1 Query", "C:\Book1.xlsx"

    And here is an helpful article about Excel automation:
    How to automate Microsoft Excel from Visual Basic

    And if there is already exits data in the target worksheet. We can use ADODB to get the data from query and copy the data into target sheets. And here is an example that copy the query data into the tab1 worksheet start from Range A5:

    Sub exportQueryADODB()
    
    Dim dbs As database
    
    Set dbs = CurrentDb
    
    
    Set rsQuery = dbs.OpenRecordset("Table1 Query")
    
    Set excelApp = CreateObject("Excel.application", "")
    excelApp.Visible = True
    Set targetWorkbook = excelApp.workbooks.Open("C:\Book1.xlsx")
    targetWorkbook.Worksheets("tab1").Range("A5").CopyFromRecordset rsQuery
    
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by MelZZ Thursday, September 17, 2015 6:24 PM
    Wednesday, September 16, 2015 3:35 AM
    Moderator

All replies

  • Hi MelZZ,

    The easy way to export the query to spreadsheet using DoCmd.TransferSpreadsheet method. However, this method wouldn't let us choose the worksheet to export. It would export the data appending last worksheet and name it automatically. We can manipulate the spreadsheet via Excel object model to rename the worksheet and delete the target worksheet.

    Code for export data to spreadsheet:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table1 Query", "C:\Book1.xlsx"

    And here is an helpful article about Excel automation:
    How to automate Microsoft Excel from Visual Basic

    And if there is already exits data in the target worksheet. We can use ADODB to get the data from query and copy the data into target sheets. And here is an example that copy the query data into the tab1 worksheet start from Range A5:

    Sub exportQueryADODB()
    
    Dim dbs As database
    
    Set dbs = CurrentDb
    
    
    Set rsQuery = dbs.OpenRecordset("Table1 Query")
    
    Set excelApp = CreateObject("Excel.application", "")
    excelApp.Visible = True
    Set targetWorkbook = excelApp.workbooks.Open("C:\Book1.xlsx")
    targetWorkbook.Worksheets("tab1").Range("A5").CopyFromRecordset rsQuery
    
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by MelZZ Thursday, September 17, 2015 6:24 PM
    Wednesday, September 16, 2015 3:35 AM
    Moderator
  • Hi MelZZ,

    The easy way to export the query to spreadsheet using DoCmd.TransferSpreadsheet method. However, this method wouldn't let us choose the worksheet to export. It would export the data appending last worksheet and name it automatically. We can manipulate the spreadsheet via Excel object model to rename the worksheet and delete the target worksheet.

    Code for export data to spreadsheet:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table1 Query", "C:\Book1.xlsx"

    And here is an helpful article about Excel automation:
    How to automate Microsoft Excel from Visual Basic

    And if there is already exits data in the target worksheet. We can use ADODB to get the data from query and copy the data into target sheets. And here is an example that copy the query data into the tab1 worksheet start from Range A5:

    Sub exportQueryADODB()
    
    Dim dbs As database
    
    Set dbs = CurrentDb
    
    
    Set rsQuery = dbs.OpenRecordset("Table1 Query")
    
    Set excelApp = CreateObject("Excel.application", "")
    excelApp.Visible = True
    Set targetWorkbook = excelApp.workbooks.Open("C:\Book1.xlsx")
    targetWorkbook.Worksheets("tab1").Range("A5").CopyFromRecordset rsQuery
    
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thank you. This one works.

    • Edited by MelZZ Thursday, September 17, 2015 6:24 PM this is an answer;
    Thursday, September 17, 2015 5:12 PM
  • Hey MelZZ, when you have some free time, you should take a look at the link below.  You'll learn a ton!!

    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

    This is also (somewhat) related.

    http://www.erlandsendata.no/english/index.php?d=envbadacimportado

    http://www.erlandsendata.no/english/index.php?d=envbadacimportdao


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, October 10, 2015 12:21 AM
  • Hello,

    I have used the above code to create a function to copy to a specific sheet in a template.  I have to two queries I need to put into to sheets into the same template.  One is Trends Table and the other is P&L. However, I am not sure how to edit the code to add a header row and limit the query results for both forms to the master record. 



    • Edited by Lavenderchan Monday, February 6, 2017 5:02 PM Clafication
    Monday, February 6, 2017 4:45 PM