Solved

CopyFromRecordset unable to handle fields with null

Posted on 2008-10-01
3
777 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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now