asked on

Code to send query result to Excel

This is the code behind a button on a form which runs a query called “m1Revenue1”. I would like to add some code which opens up Excel and writes the query result to Sheet1 of the Excel workbook. Thanks.

Private Sub Cmd4_Q4_Click()
On Error GoTo Err_Cmd4_Q4_Click

    Dim stDocName As String

    stDocName = "m1Revenue1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Cmd4_Q4_Click:
    Exit Sub

Err_Cmd4_Q4_Click:
    MsgBox Err.Description
    Resume Exit_Cmd4_Q4_Click
    
End Sub

Open in new window

Rey Obrero (Capricorn1)

you can use this codes

docmd.transferspreadsheet acexport,8, "m1Revenue1","c:\myfolder\mYexcel.xls", true

RishiSingh05

ASKER

We don't have access to the C drive. I entered the path to my shared drive. The query ran but threw a message "file creation failure". My shared drive path is a bit convoluted. Is it not possible to write code which opens up an Excel workbook, write the query output to it on Sheet1? I can then save the Excel to wherever I want. Thanks.

Rey Obrero (Capricorn1)

< I can then save the Excel to wherever I want. Thanks.>

then you should have no problem using the codes posted above, just change the path

RishiSingh05

ASKER

but first I need to output the query result to an excel worksheet. Then I will manually save it somewhere.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RishiSingh05

ASKER

I included it in my existing code - pls see below. It works. I have a follow up question but I will post it as a new question. Thanks.

Private Sub Cmd4_Q4_Click()
On Error GoTo Err_Cmd4_Q4_Click

    Dim stDocName As String

    stDocName = "m1Revenue1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Dim rs As DAO.Recordset
Dim xlObj As Object, Sheet As Object, iCol As Integer

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add
    xlObj.Visible = True
    Set rs = CurrentDb.OpenRecordset("m1Revenue1")
    Set Sheet = xlObj.activeworkbook.workSheets("Sheet1")
        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A2").CopyFromRecordset rs  'copy the data
    
    
Exit_Cmd4_Q4_Click:
    Exit Sub

Err_Cmd4_Q4_Click:
    MsgBox Err.Description
    Resume Exit_Cmd4_Q4_Click

End Sub

Open in new window