Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

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

0
RishiSingh05
Asked:
RishiSingh05
  • 3
  • 3
1 Solution
 
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now