Retrieve data from recordset into ListObject

Stevenn

Active Member
Joined
Feb 8, 2012
Messages
259
What is the best way to print data from a recordset into a ListObject without first deleting the already existing ListObject and converting the new range into a ListObject. I want to avoid this way to do it because all my formulas in other sheets will be result in errors if the already existing ListObject doesn't exist while I delete it and create a new one with the same name. Could it somehow be possible to tell the formulas in the other sheets not to react to a non-existing named ListObject until I've created the new ListObject?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Hi Steven

1st thing is to clear the DataBodyRange. Then you can drop your recordset straight into the table.
Code:
    With Sheet1.ListObjects("Table1")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
And then all you have to do is drop the recordset; so if your table DataBodyRange starts in A2:
Code:
Call Sheet1.Range("A2").CopyFromRecordset(recData)
 

Stevenn

Active Member
Joined
Feb 8, 2012
Messages
259
Hi Steven

1st thing is to clear the DataBodyRange. Then you can drop your recordset straight into the table.
Code:
    With Sheet1.ListObjects("Table1")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
And then all you have to do is drop the recordset; so if your table DataBodyRange starts in A2:
Code:
Call Sheet1.Range("A2").CopyFromRecordset(recData)
It's a good solution, but what if I want two columns right next to the other columns to contain formulas?
 

Tom_H

New Member
Joined
Feb 9, 2017
Messages
23
This do not copy recordset to listobject.
It works if A2 is the first cell in the listobject.
But if I move the listobject on the sheet , the code will not work.
Is there a way to actually copy rs to listobject?

My workaround..
Code:
Set rng = Range(tbl.Range.Cells(1).Address).Offset(1, 0) 
rng.CopyFromRecordset rs
Hm.. ok this is about Access database recordset. :eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,099,055
Messages
5,466,307
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top