Export query to Excel without saving

[JiTS]

Hi,

I searched the forum for my problem but I didn't find the solution.

I want to export a query to Excel, but without saving the Excel file. It's similar like copying manually the query results in Access and paste them in a new Excel workbook/sheet.

I would like to automate this. I also want to change automatically the column heads into names which I want.

Thanks in advance!

 

[KeithG]

You will have to use VBA to open up a recordset, a instance of excel, and a excel spreadsheet and then copy the field values to excel from the recordset.

 

[JiTS]

A couple of hours later and I am almost there!

The query results will be exported to Excel, that's ok. But the query contains a dynamic parameter for generating results for a specific team.

How can I add this parameter to get the correct team results in stead of all results?


I used the following code:

Dim dbs As DAO.Database
    
    Dim rstGetRecordSet As Recordset
    
    Dim objXL As Object
    Dim objCreateWkb As Object
    Dim objActiveWkb As Object
    
    Set dbs = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    Set objCreateWkb = objXL.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook
    
    objXL.Visible = True
    objActiveWkb.Sheets.Add
    objActiveWkb.Worksheets(1).Name = "TeamMain"
    
    Set rstGetRecordSet = dbs.OpenRecordset("qryTeamMainToExcel")
    
    objActiveWkb.Worksheets("TeamMain").Cells(1, 1).Value = "Test"
    objActiveWkb.Worksheets("TeamMain").Cells(2, 1).CopyFromRecordset rstGetRecordSet
    
    Set objActiveWkb = Nothing
    Set objCreateWkb = Nothing
    Set objXL = Nothing
    rstGetRecordSet.Close
    dbs.Close
    Set rstGetRecordSet = Nothing
    Set dbs = Nothing

 

[JiTS]

Found it by myself!

You need to add the following code to load the parameters which are set in the query:

For Each prm In qdf.Parameters
     prm.Value = Eval(prm.Name)
Next prm