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

RishiSingh05Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
Sub exportExcel()
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

end sub
0
 
Rey Obrero (Capricorn1)Commented:
you can use this codes

docmd.transferspreadsheet acexport,8, "m1Revenue1","c:\myfolder\mYexcel.xls", true
0
 
RishiSingh05Author Commented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
< 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
0
 
RishiSingh05Author Commented:
but first I need to output the query result to an excel worksheet.  Then I will manually save it somewhere.
0
 
RishiSingh05Author Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.