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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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")

    qdf.Close

    qdf1.Close

    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"

    End
    Sub


    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

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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"
aWorkbook.Close
excelApp.Quit

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