RishiSingh05
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
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.
< 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
then you should have no problem using the codes posted above, just change the path
ASKER
but first I need to output the query result to an excel worksheet. Then I will manually save it somewhere.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
docmd.transferspreadsheet acexport,8, "m1Revenue1","c:\myfolder\