Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Retrieve data from recordset into ListObject

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Retrieve data from recordset into ListObject

    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?

  2. #2
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,656
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Retrieve data from recordset into ListObject

    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)
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve data from recordset into ListObject

    Quote Originally Posted by Jon von der Heyden View Post
    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?

  4. #4
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,656
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Retrieve data from recordset into ListObject

    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
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  5. #5
    New Member
    Join Date
    Feb 2017
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve data from recordset into ListObject

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •