Forum Discussion

mzeller1776's avatar
mzeller1776
Copper Contributor
Dec 13, 2024
Solved

Export MS Access query results to Excel

Hello,

I'm trying to set it up so that a user can open a form in MS Access, click a control button, and it will run two queries automatically and export the two query results into new Excel workbooks in a specific location. I've been looking at doing an Event Procedure and using VBA to do this, but not having any luck. Can someone please help. I tried doing the following for the EventProcedure:

Private Sub ExportToExcel_Click()
DoCmd.TransferSpreadsheet acExport, , "Qry Confirmations Match", "I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification", True
DoCmd.TransferSpreadsheet acExport, , "Qry Confirmations No Match", "I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification", True
End Sub

Thanks you!

excel
Macros and VBA
  • arnel_gp's avatar
    arnel_gp
    Dec 14, 2024

    you are missing something there on your code, this is the correct syntax:

    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"Qry Confirmations Match","I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification.xlsx", True

     

    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"Qry Confirmations No Match", "I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification.xlsx", True

     

2 Replies

Sort By
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Dec 14, 2024

    you are missing something there on your code, this is the correct syntax:

    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"Qry Confirmations Match","I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification.xlsx", True

     

    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"Qry Confirmations No Match", "I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification.xlsx", True

     

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Dec 14, 2024

     

    path="D:\wwwroot\HA\WEB\Bsbm\stu60\vba\up\upload\20240229110373547354.xlsx"

     

    'Create a new ADO connection
        Set conn = CreateObject("ADODB.Connection")
        
        ' Connection string for Access database
        conn.Open

     

     "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &path
        
        ' SQL query to create a new workbook (example2.xlsx) and populate it with data from a table in Access
        strSQL = "SELECT * INTO [Excel 12.0 Xml;HDR=YES;DATABASE=D:\wwwroot\HA626046\WEB\Bsbm\stu60\vba\up\upload\example2.xlsx].[Sheet1] FROM stu2;"
        
        ' Execute the SQL query
        conn.Execute strSQL
        
        ' Close connection
        conn.Close
        
        ' Clean up
        Set conn = Nothing

Resources