Recordset contents to excel using access vba

Question

I'm trying to write record set contents to excel sheet. My code is not working when trying to move record set contents to Movefirst. My vba code

   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   strSQL = "SELECT * FROM " & qrytable & ""
   rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

   Set wsSheet1 = wb.Sheets(conSHT_NAME)
   wsSheet1.Cells.ClearContents
   wsSheet1.Select
   For i = 1 To rst.Fields.Count
     wsSheet1.Cells(1, i) = rst.Fields(i - 1).Name
   Next i

   If rst.EOF Then
   MsgBox "inside rst"
   rst.MoveFirst
   wsSheet1.Range("a2").CopyFromRecordset rst
   End If

   wsSheet1.Columns("A:Q").EntireColumn.AutoFit
   rst.Close

The condition If rst.EOF is becomes true and when i'm trying to move record set to rst.Movefirst the debugging control is moving out of the method and moving to the method from where i'm calling this method and not writing contents to excel.

Are you sure you're actually getting any data in your recordset? That would explain why EOF is true when you run your code. — Bob Tway
you normally dont move first when using the .CopyFromRecordset method. Just do your headers and then call the .CopyFromRecordset rst on a Range("A2") that's it. You only move first, next ,last when iterating over your recordset but you ain't doing that in the above code. So just comment out the entire if and move statements and just leave .CopyFromRecordset — user2140173
@Matt Thrower My record set is showing null. but when i'm running the query SELECT * from qrytable from query wizard the data is retriving. Why my recordset is null? — sam
@ me how , I checked what you specified before, but the contents are not writing to excel. sheet is showing as blank. — sam
What's qrytable? It's a variable, but in the code you're displaying it's not set anywhere. If it's empty then your code will be trying to run "SELECT * from" against the database - which will, of course, return nothing. — Bob Tway

Sorcefyre Sorcefyre · Accepted Answer · 2015-01-14T20:25:58

Test for a null recordset with the following:

If (rst.BOF And rst.EOF) Then
    rst.Close: set rst = Nothing 
Else
    rst.MoveFirst 

    rst.CopyFromRecordset rst
End If

Recordset contents to excel using access vba

1 Answers