Revision history [back]

To determine if a recordset is complete, you need to check for the end of the recordset. You can accomplish this by checking the recordCount property of the recordset. If the recordCount is zero or negative, then the recordset is empty. Another way to determine if a recordset is complete is to navigate through all the records in the recordset using the MoveNext method, and checking if the EOF (End of File) property is true.

To transfer the recordset to an Excel sheet, you can follow these steps:

1. Create a new Excel workbook or open an existing workbook.
2. Activate the worksheet where you want to copy the data.
3. Set the column names in the worksheet if needed.
4. Loop through the recordset and copy the data into the worksheet cell by cell.
5. After all the data has been copied, save the Excel workbook.

Here is some sample VBA code that demonstrates how to copy the recordset to an Excel sheet:

Sub CopyDataToExcel()
    Dim rs As ADODB.Recordset
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim i As Long, j As Long

    'Open recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM MyTable", CurrentProject.Connection

    'Open Excel Application
    Set xlApp = New Excel.Application
    xlApp.Visible = True

    'Open workbook and worksheet
    Set xlWB = xlApp.Workbooks.Add
    Set xlWS = xlWB.Worksheets(1)

    'Add column names to the worksheet
    For i = 0 To rs.Fields.Count - 1
        xlWS.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i

    'Copy data to Excel sheet
    i = 2
    Do Until rs.EOF
        For j = 0 To rs.Fields.Count - 1
            xlWS.Cells(i, j + 1).Value = rs.Fields(j).Value
        Next j
        i = i + 1
        rs.MoveNext
    Loop

    'Save workbook and close Excel application
    xlWB.SaveAs "C:\MyWorkbook.xlsx"
    xlWB.Close
    xlApp.Quit

    'Clean up objects
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing
    rs.Close
    Set rs = Nothing
End Sub