Solved

CopyFromRecordset unable to handle fields with null

Posted on 2008-10-01
3
814 Views
Last Modified: 2013-11-29
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

0
Comment
Question by:tipvernonia
  • 2
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22617476

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
 

Author Comment

by:tipvernonia
ID: 22623877
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22625004
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question