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

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

Answer 1

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.

Answer 2

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

Answer 3

I miss some essential information here: What is the error code?

---

Best regards, George

Answer 4

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.

Answer 5

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

Answer 6

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