Solved

Code to output query to Excel

Posted on 2011-02-14
8
543 Views
Last Modified: 2012-05-11
This question is further to a previous question I asked and for which a solution was provided by capricorn:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26820656.html

In the first part of my question (link above) I run a query and Cap has provided code which writes the query output to Sheet1 of an Excel workbook.  I now want to run a second query and would like to output the query result to Excel starting in the row just after the last row (record) of the first query output.  Thanks.
0
Comment
Question by:RishiSingh05
  • 4
  • 4
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34891859

    Dim rs As DAO.Recordset
Dim xlObj As Object, Sheet As Object, iCol As Integer
Dim lastRow as long

    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
   
lastRow=Sheet.usedrange.rows.count

     Set rs = CurrentDb.OpenRecordset("query2")
         For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(lastRow+1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A" & lastRow +2).CopyFromRecordset rs  'copy the data    




0
 

Author Comment

by:RishiSingh05
ID: 34891943
Here is the code behind button2 which runs query2 ...

Which part of your provided code above do I need to use?  I don't want to re-run the query1 (m1Revenue1) as that has been run already and copied to Excel already.  Excel is still open as I run query2.

Private Sub Cmd5_Q5_Click()
On Error GoTo Err_Cmd5_Q5_Click

    Dim stDocName As String

    stDocName = "m1Revenue2_falloff"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
Exit_Cmd5_Q5_Click:
    Exit Sub

Err_Cmd5_Q5_Click:
    MsgBox Err.Description
    Resume Exit_Cmd5_Q5_Click
    
End Sub

Open in new window

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34891964
just run it one time...

    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
   
lastRow=Sheet.usedrange.rows.count

     Set rs = CurrentDb.OpenRecordset("m1Revenue2_falloff")
         For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(lastRow+1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A" & lastRow +2).CopyFromRecordset rs  'copy the data    


0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34891968
and you don't need to open the query.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:RishiSingh05
ID: 34892042
ok now I understand  .... thanks.
0
 

Author Comment

by:RishiSingh05
ID: 34897242
I am trying to add a third query but I am not getting it to work.  Pls see code below.
Private Sub Cmd_RunSelectQueries_Click()


    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
   
lastRow = Sheet.usedrange.rows.Count

     Set rs = CurrentDb.OpenRecordset("m1Revenue2_falloff")
         For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(lastRow + 1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A" & lastRow + 2).CopyFromRecordset rs 'copy the data


     Set rs = CurrentDb.OpenRecordset("Members1")
         For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(lastRow + 1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A" & lastRow + 2).CopyFromRecordset rs 'copy the data
  
End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34897773
you need to find the lastRow again



Private Sub Cmd_RunSelectQueries_Click()


    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
   
lastRow = Sheet.usedrange.rows.Count

     Set rs = CurrentDb.OpenRecordset("m1Revenue2_falloff")
         For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(lastRow + 1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A" & lastRow + 2).CopyFromRecordset rs 'copy the data

lastRow = Sheet.usedrange.rows.Count  ' <<< ADD HERE
     Set rs = CurrentDb.OpenRecordset("Members1")
         For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(lastRow + 1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A" & lastRow + 2).CopyFromRecordset rs 'copy the data
  
End Sub

Open in new window

0
 

Author Comment

by:RishiSingh05
ID: 34906661
Thanks.  I was also able to figure it out.  Sorry for the extra work !!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now