Solved

Code to send query result to Excel

Posted on 2011-02-14
6
564 Views
Last Modified: 2012-05-11
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
Comment
Question by:RishiSingh05
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34891020
you can use this codes

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

Author Comment

by:RishiSingh05
ID: 34891251
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34891286
< 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:RishiSingh05
ID: 34891325
but first I need to output the query result to an excel worksheet.  Then I will manually save it somewhere.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34891381
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
 

Author Comment

by:RishiSingh05
ID: 34891689
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question