Hello all,
I am trying to populate a spreadsheet with data from access query. Even though the recordset(input file) has approximately 49 records, only 1 record is written on the spreadsheet. What am I doing wrong?

Below is my code:

Set rsin = M.DB.OpenRecordset("qBiWeeklyPeriodCombined", dbOpenDynaset)
         
    If rsin.RecordCount < 0 Then
       MsgBox "There are not records for your selection String/Criteria", vbInformation, "PTS System"
       Exit Function
    End If
     
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open("C:\BiWeeklyPeriod.xls")
    Set xlWksht = xlWbk.Worksheets(1)
    
    xlWksht.UsedRange.ClearContents
            
    Dim ii As Long

    ii = 4
    ii = ii + 1
    xlWksht.Cells(ii, 1).Value = "Req" & Chr(10) & "No"
    xlWksht.Cells(ii, 2).Value = "Description"
    xlWksht.Cells(ii, 3).Value = "PL" & Chr(10) & "Pgr"
    xlWksht.Cells(ii, 4).Value = "Client Name" & Chr(10) & "Status"
    xlWksht.Cells(ii, 5).Value = "Current"
    xlWksht.Cells(ii, 6).Value = "Tot Hrs"
    xlWksht.Cells(ii, 7).Value = "Estimated" & Chr(10) & "Actual" & Chr  (10) & "Start Date"
    xlWksht.Cells(ii, 8).Value = "End Date"
        
    Do Until rsin.EOF = True
       ii = 6
       ii = ii + 1
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(8, 1).Value = "Comments:" & Chr(10) & rsin![Comments]
       xlWksht.Range("A8:J8").MergeCells = True
       rsin.MoveNext
    Loop

Thanks
tgifgemini.

Sorry for my missinformation. The recordset is advancing and picking up 49 records, but only 1 record prints on the spreadsheet.
tgifgemini.

You are reading all the records, but you are always putting them on line 7, each one overwrites the one before. because ii= 6 is within your loop , put ii= 6 before your loop, before " do until eof "

Hi tgif,

what Plusplus is tellenig is true, change this part:

Do Until rsin.EOF = True
    ii = 6
    ii = ii + 1

to :

ii=6
Do Until rsin.EOF = True
       ii = ii + 1

Regards
Veena

Thanks, you good folks. I didn't even catch the "ii = 6" inside the loop. Anyway, it worked like a charm.
Thanks again.
tgifgemini.

This article has been dead for over six months. Start a new discussion instead.