Retrieve data from recordset into ListObject

[Stevenn]

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?

 

[Jon von der Heyden]

Hi Steven

1st thing is to clear the DataBodyRange. Then you can drop your recordset straight into the table.

    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:

Call Sheet1.Range("A2").CopyFromRecordset(recData)

 

[Stevenn]

Hi Steven

1st thing is to clear the DataBodyRange. Then you can drop your recordset straight into the table.

    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:

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?

 

[Jon von der Heyden]

Have you tried it? The ListObject should still keep the formulae in memory and re-apply them after you drop the recordset.

More info here: Clearing Excel Tables - by Colin Legg

 

[Tom_H]

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..

Set rng = Range(tbl.Range.Cells(1).Address).Offset(1, 0) 
rng.CopyFromRecordset rs

Hm.. ok this is about Access database recordset.