CopyFromRecordset unable to handle fields with null

Method 'CopyFromRecrodset' of ojbect 'Range' failed.  If I remove fields containing null values, this code works.  The data recordset can and will have fields with a null values is there a workaround?  I really need a quick method to export a large recordset to Excel.

   
CellCnt = 1
        For Each fld In rs.Fields
               xlSheet.Cells(1, CellCnt).Value = fld.Name
               xlSheet.Cells(1, CellCnt).Interior.ColorIndex = 33
               xlSheet.Cells(1, CellCnt).Font.Bold = True
               xlSheet.Cells(1, CellCnt).BorderAround xlContinuous
               CellCnt = CellCnt + 1
        Next fld
    
        rs.MoveFirst
        xlSheet.Cells(2, 1).CopyFromRecordset rs
        DoEvents
 
    ' Auto fit columns.
        xlSheet.Cells.EntireColumn.AutoFit
        xlSheet.Cells.Select
        xlSheet.Cells.AutoFilter

Open in new window

tipvernoniaAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try using the nz function in the query for the field that has null values
use the query for the recordset and use the copyfromrecordset to fill the excel file with values
0
 
Rey Obrero (Capricorn1)Commented:

you will need to individually fill out the cells with the specific  rs.fields value

             xlSheet.Cells(2, 1).value=nz(rs("fieldname"),"")  '< for text field
0
 
tipvernoniaAuthor Commented:
I already used this method.  I was looking for a faster method.  When there are 10,000 row with 28 columns this method is very slow.  
0
All Courses

From novice to tech pro — start learning today.