VBA to Export recordset

[tuktuk]

hey there,

here is my code that i use to export to excel.

i know that it steps through each cell to paste the header values and then pastes the entire recordset for the dataset.

what is the best way to alter the code to ensure it does not break if the recorset is null?

thanks
tuk

Set rst = dbs.OpenRecordset(strExport, dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then

      rst.MoveFirst

      If blnHeaderRow = True Then
            For lngColumn = 0 To rst.Fields.Count - 1
                  xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
            Next lngColumn
            Set xlc = xlc.Offset(1, 0)
      End If

xlc.CopyFromRecordset rst

End If

 

[Trevor G]

I use this code with a database when exporting into Excel for this specific project it is using ADO and works fine.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ws As Excel.Application
Dim strSQL As String
Dim i As Long

strSQL = "SELECT * FROM qryNametoExport"
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rst.MoveFirst
MsgBox rst.RecordCount

Set ws = CreateObject("Excel.Application")
ws.Workbooks.Add
ws.Columns("A:A").Select
ws.Selection.NumberFormat = "0"
ws.Selection.ColumnWidth = 18.14
ws.Columns("A:A").EntireColumn.AutoFit
For i = 0 To rst.Fields.Count - 1
ws.ActiveCell.Offset(0, i).Value = rst.Fields(i).Name
Next
ws.Range("a2").Select
Do Until rst.EOF
For i = 0 To rst.Fields.Count - 1
ws.ActiveCell.Offset(0, i).Value = rst.Fields(i).Value
Next
ws.ActiveCell.Offset(1, 0).Select
rst.MoveNext
Loop
rst.Close
ws.Columns("A:v").EntireColumn.AutoFit
ws.Visible = True

 

[james_lankford]

put

if rst is not nothing 
    If rst.EOF = False And rst.BOF = False Then
    end if
end if

or it may be
if not rst is nothing
I forget