none
How to loop through records in a query and write to Excel? RRS feed

  • Question

  • I'm trying to loop through all records in a query and write the records to a newly created Excel worksheet.  The code below should be pretty close, but it's not going to the second line of data in the query.  In other words, it's not going to the second (or third or forth) line in the query.  I think the problem comes from here.

    For i = 1 To rsCount

    Here is my script.

    Private Sub Generate_Click()
    
    'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    
    'Step 2: Identify the database and query
    Set MyDatabase = CurrentDb
    Set MyQueryDef = MyDatabase.QueryDefs("qryOpen") 'Query name in the database
    'Step 3: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
    'Step 4: Clear previous contents
    
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim rsCount As Integer
    Dim queryNameOrSQL As String
    
    queryNameOrSQL = "qryOpen"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(queryNameOrSQL)
    
    rsCount = rs.RecordCount
    
    
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    
    With xlApp
        .Visible = True
        .Workbooks.Open "C:\Users\path\Template.xlsb", True, False
        '.Workbooks.Add
        .Sheets("Template").Select
        
            For i = 1 To rsCount
                'Step 5: Copy the recordset to Excel
                    .Sheets("Template").Select
                    .Sheets("Template").Copy After:=.Sheets("Template")
                    .Activesheet.Name = MyRecordset.Fields("rc_nr").Value
    
                .Activesheet.Range("C3").Value = MyRecordset.Fields("period").Value
                .Activesheet.Range("D3").Value = MyRecordset.Fields("rc_nr").Value
                .Activesheet.Range("I3").Value = MyRecordset.Fields("rc_name").Value
                .Activesheet.Range("M3").Value = MyRecordset.Fields("rc_LoB").Value
                .Activesheet.Range("C7").Value = MyRecordset.Fields("rc_RBP").Value
                .Activesheet.Range("L7").Value = MyRecordset.Fields("rc_desc").Value
                .Activesheet.Range("O7").Value = MyRecordset.Fields("rc_products").Value
                .Activesheet.Range("C10").Value = MyRecordset.Fields("rc_entity").Value
                .Activesheet.Range("D10").Value = MyRecordset.Fields("rc_label_type1").Value
                
                
                .Sheets("Template").Select
            Next i
        
        xlApp.Cells.EntireColumn.AutoFit
    End With
    'MsgBox "Query has been successful", vbInformation, "Sample"
        
    End Sub
    
    

    Basically, I'm trying to open a specific Excel file, select a sheet named 'Template' which has all the formatting that I need, copy and rename this, then copy the first row of the query into specific cells in the sheet.  Then, select the 'Template' sheet, rename it, and copy the second row of the query into the sheet.  Then the third row of the query, then the forth row of the query, and so on, until it copies the last row of the query.  I'm guessing there must be some kind of RecordSet count, or something like that.


    MY BOOK

    Wednesday, May 11, 2016 3:45 PM

Answers

  • Among your other problems, your code defines and sets two different Recordset objects, one named "MyRecordset" and one named "rs".  The looping code, even if corrected, is looping with recordset rs, but the code inside the loop is getting values from MyRecordset.  You should only be using one recordset object (and only one Database object, and -- if used -- one QueryDef object).


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, May 11, 2016 6:12 PM
  • Thanks.  I missed that.  Now it works.

    I had to change this:

    MyRecordset.Fields

    To this:

    rs.Fields

    Here is the final loop, for the benefit of others.

            Do While (Not rs.EOF)
                    .Sheets("Template").Select
                    .Sheets("Template").Copy After:=.Sheets("Template")
                    .Activesheet.Name = rs.Fields("rc_name").Value
                .Activesheet.Range("C3").Value = rs.Fields("period").Value
                .Activesheet.Range("D3").Value = rs.Fields("rc_nr").Value
                .Activesheet.Range("I3").Value = rs.Fields("rc_name").Value
                .Activesheet.Range("M3").Value = rs.Fields("rc_LoB").Value
                .Activesheet.Range("C7").Value = rs.Fields("rc_RBP").Value
                .Activesheet.Range("L7").Value = rs.Fields("rc_desc").Value
                .Activesheet.Range("O7").Value = rs.Fields("rc_products").Value
                .Activesheet.Range("C10").Value = rs.Fields("rc_entity").Value
                .Activesheet.Range("D10").Value = rs.Fields("rc_label_type1").Value
                .Sheets("Template").Select
            rs.MoveNext
            Loop

    Thanks IMB-HB!!


    MY BOOK

    Wednesday, May 11, 2016 6:21 PM

All replies

  • Set rs = db.OpenRecordset(queryNameOrSQL)

    Hi ryguy,

    You can loop through the recordset:

      Do While (Not rs.EOF)
        ....
        'do your things, write to Excel
        ....
        rs.MoveNext
      Loop

    I do not know too much about Excel programming. You have to move to the next Excel line, probably you can use an increment of i to do that.

    Imb.

    Wednesday, May 11, 2016 4:00 PM
  • Hummmm, that doesn't work for me.

            Do While (Not rs.EOF)
                'Step 5: Copy the recordset to Excel
                    .Sheets("Template").Select
                    .Sheets("Template").Copy After:=.Sheets("Template")
                    .Activesheet.Name = MyRecordset.Fields("rc_nr").Value
    
                .Activesheet.Range("C3").Value = MyRecordset.Fields("period").Value
                .Activesheet.Range("D3").Value = MyRecordset.Fields("rc_nr").Value
    
    etc.....
    
                .Activesheet.Range("D10").Value = MyRecordset.Fields("rc_label_type1").Value
                
                
                .Sheets("Template").Select
            Loop

    The problem is that Access is not looping to the next record set.  There must be another way to do it.  Any other ideas?


    MY BOOK

    Wednesday, May 11, 2016 4:09 PM
  • I miss some essential information here: What is the error code?

    Best regards, George

    Wednesday, May 11, 2016 4:36 PM

  • The problem is that Access is not looping to the next record set.  There must be another way to do it.  Any other ideas?

    Hi ryguy,

    See my previous answer.

    You did not add the line:   rs.MoveNext, to propagate the loop.

    Imb.

    Wednesday, May 11, 2016 5:37 PM
  • Among your other problems, your code defines and sets two different Recordset objects, one named "MyRecordset" and one named "rs".  The looping code, even if corrected, is looping with recordset rs, but the code inside the loop is getting values from MyRecordset.  You should only be using one recordset object (and only one Database object, and -- if used -- one QueryDef object).


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, May 11, 2016 6:12 PM
  • Thanks.  I missed that.  Now it works.

    I had to change this:

    MyRecordset.Fields

    To this:

    rs.Fields

    Here is the final loop, for the benefit of others.

            Do While (Not rs.EOF)
                    .Sheets("Template").Select
                    .Sheets("Template").Copy After:=.Sheets("Template")
                    .Activesheet.Name = rs.Fields("rc_name").Value
                .Activesheet.Range("C3").Value = rs.Fields("period").Value
                .Activesheet.Range("D3").Value = rs.Fields("rc_nr").Value
                .Activesheet.Range("I3").Value = rs.Fields("rc_name").Value
                .Activesheet.Range("M3").Value = rs.Fields("rc_LoB").Value
                .Activesheet.Range("C7").Value = rs.Fields("rc_RBP").Value
                .Activesheet.Range("L7").Value = rs.Fields("rc_desc").Value
                .Activesheet.Range("O7").Value = rs.Fields("rc_products").Value
                .Activesheet.Range("C10").Value = rs.Fields("rc_entity").Value
                .Activesheet.Range("D10").Value = rs.Fields("rc_label_type1").Value
                .Sheets("Template").Select
            rs.MoveNext
            Loop

    Thanks IMB-HB!!


    MY BOOK

    Wednesday, May 11, 2016 6:21 PM