Trying to Export results from multiple parameterized queries from Access Form to Excel via TransferSpreadsheet acExport RRS feed

  • Question

  • Hello, I'm Trying to cobble together a solution at my office using SharePoint and Office 2010.

    The setup involves doing some datapulls from SharePoint to Access and then pushing some
    of those results to Excel for calculations\formatting based on 2 (dynamic) variables.

    I have the data successfully auto-flowing from SharePoint to Access.

    I have some parameterized queries that are sitting on top of the "SharePoint
    tables" in Access and those queries prompt for 2 variables. Org and Calendar Year [CY] (same spelling/capitalization in both queries). Standalone, the queries execute fine.

    Using VBA, I created a form with a button. Behind that button is the following code.

    Private Sub Command0_Click()

    strPath= "C:\Users\DC24955\Desktop\Export Test.xlsx"

    Dim qdf As DAO.QueryDef

    Dim qdf1 As DAO.QueryDef

    Dim dbs As DAO.Database

    Set dbs = CurrentDb

    Set qdf = dbs.QueryDefs("Qry_IPP_Develop_and_Doc_Perf_Stnds")

    Set qdf1 = dbs.QueryDefs("Qry_IPP_New_Employees")



    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Qry_IPP_Develop_and_Doc_Perf_Stnds", strPath, True, "IPP_Dev_and_Doc_Perf_Stnds"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Qry_IPP_New_Employees", strPath, True, IPP_New_Emp"


    After several days, I was able to work out the bugs so that when I click the form button, the code executes correctly and the queries populate excel... with the right results… My only complaint is that even though both queries have parameters with the same name, I get 2 sets of prompts. Is there any way (with minimal coding changes), to have the prompts come up only 1 time and have the values provided in the first prompt, passed along as variables to the subsequent queries? By the time I am done, I will be executing over 12 different saved queries. All of them will be driven by Org and CY and so it would be great is there was a simple work-around to retain those values and pass them to the prompt. Thank you

    • Edited by Dave_VA1234 Thursday, December 17, 2015 6:12 PM
    Thursday, December 17, 2015 5:10 PM


  • Hi Dave,

    To avoid the multiple prompt via parameter query, we can use Excel object model to copy the record from Access to Excel.

    Here is a code demo that prompt use to input the parameter and then initialize the query's parameter based on the input. Then we can get the query result and copy it into spreadsheets.

    Sub ExportQueryToWorksheet()
    Dim myQuery As QueryDef
    Dim rs As Recordset
    Set myQuery = CurrentDb.QueryDefs("NewQueryDef")
    ret = InputBox("Type id here!", "Input")
    myQuery.Parameters("id").Value = ret
    Set rs = myQuery.OpenRecordset
    Set excelApp = CreateObject("Excel.Application")
    Set aWorkbook = excelApp.Workbooks.Add
    Set aSheet = aWorkbook.Sheets(1)
    aSheet.Range("A1").CopyFromRecordSet rs
    aWorkbook.SaveAs "C:\book1.xlsx"
    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.

    Friday, December 18, 2015 9:47 AM