Forum Discussion

mzeller1776's avatar
mzeller1776
Brass 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 workbook...
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

     

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