How do I reference tables in Excel using VBA?

Question

Is it possible in Excel VBA to reference a named table?

Hypothetically this could be...

Sheets("Sheet1").Table("A_Table").Select

I have seen some mention of tables being a list object but I'm not sure if that is the same thing.

Solution 1

Converting a range to a table as described in this answer:

Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

Solution 2

The OP asked, is it possible to reference a table, not how to add a table. So the working equivalent of

Sheets("Sheet1").Table("A_Table").Select

would be this statement:

Sheets("Sheet1").ListObjects("A_Table").Range.Select

or to select parts (like only the data in the table):

Dim LO As ListObject
Set LO = Sheets("Sheet1").ListObjects("A_Table")
LO.HeaderRowRange.Select        ' Select just header row
LO.DataBodyRange.Select         ' Select just data cells
LO.TotalsRowRange.Select        ' Select just totals row

For the parts, you may want to test for the existence of the header and totals rows before selecting them.

And seriously, this is the only question on referencing tables in VBA in SO? Tables in Excel make so much sense, but they're so hard to work with in VBA!

Solution 3

A "table" in Excel is indeed known as a ListObject.

The "proper" way to reference a table is by getting its ListObject from its Worksheet i.e. SheetObject.ListObjects(ListObjectName).

If you want to reference a table without using the sheet, you can use a hack Application.Range(ListObjectName).ListObject.

NOTE: This hack relies on the fact that Excel always creates a named range for the table's DataBodyRange with the same name as the table. However this range name can be changed...though it's not something you'd want to do since the name will reset if you edit the table name! Also you could get a named range with no associated ListObject.

Given Excel's not-very-helpful 1004 error message when you get the name wrong, you may want to create a wrapper...

Public Function GetListObject(ByVal ListObjectName As String, Optional ParentWorksheet As Worksheet = Nothing) As Excel.ListObject
On Error Resume Next

    If (Not ParentWorksheet Is Nothing) Then
        Set GetListObject = ParentWorksheet.ListObjects(ListObjectName)
    Else
        Set GetListObject = Application.Range(ListObjectName).ListObject
    End If

On Error GoTo 0 'Or your error handler

    If (Not GetListObject Is Nothing) Then
        'Success
    ElseIf (Not ParentWorksheet Is Nothing) Then
        Call Err.Raise(1004, ThisWorkBook.Name, "ListObject '" & ListObjectName & "' not found on sheet '" & ParentWorksheet.Name & "'!")
    Else
        Call Err.Raise(1004, ThisWorkBook.Name, "ListObject '" & ListObjectName & "' not found!")
    End If

End Function

Also some good ListObject info here.

Solution 4

In addition, it's convenient to define variables referring to objects. For instance,

Sub CreateTable()
    Dim lo as ListObject
    Set lo = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes)
    lo.Name = "Table1"
    lo.TableStyle = "TableStyleLight2"
    ...
End Sub

You will probably find it advantageous at once.

Solution 5

In addition to the above, you can do this (where "YourListObjectName" is the name of your table):

Dim LO As ListObject
Set LO = ActiveSheet.ListObjects("YourListObjectName")

But I think that only works if you want to reference a list object that's on the active sheet.

I found your question because I wanted to refer to a list object (a table) on one worksheet that a pivot table on a different worksheet refers to. Since list objects are part of the Worksheets collection, you have to know the name of the worksheet that list object is on in order to refer to it. So to get the name of the worksheet that the list object is on, I got the name of the pivot table's source list object (again, a table) and looped through the worksheets and their list objects until I found the worksheet that contained the list object I was looking for.

Public Sub GetListObjectWorksheet()
' Get the name of the worksheet that contains the data
' that is the pivot table's source data.

    Dim WB As Workbook
    Set WB = ActiveWorkbook

    ' Create a PivotTable object and set it to be
    ' the pivot table in the active cell:
    Dim PT As PivotTable
    Set PT = ActiveCell.PivotTable

    Dim LO As ListObject
    Dim LOWS As Worksheet

    ' Loop through the worksheets and each worksheet's list objects
    ' to find the name of the worksheet that contains the list object
    ' that the pivot table uses as its source data:
    Dim WS As Worksheet
    For Each WS In WB.Worksheets
        ' Loop through the ListObjects in each workshet:
        For Each LO In WS.ListObjects
            ' If the ListObject's name is the name of the pivot table's soure data,
            ' set the LOWS to be the worksheet that contains the list object:
            If LO.Name = PT.SourceData Then
                Set LOWS = WB.Worksheets(LO.Parent.Name)
            End If
        Next LO
    Next WS

    Debug.Print LOWS.Name

End Sub

Maybe someone knows a more direct way.

View more solutions

Comments

[AndrewD]

BTW Don't expect to find the table DataBodyRange range names in Application.Names! But they can definitely be accessed using Application.Range(...).

[tm-]

This way seems to work only if workbook containing needed table is currently active. If you somehow run code with another file active, this method will fail.. Same approach as for ActiveSheet object.

[Vikky]

how can we use tables in VBA query like we did for Sheets for example "select * from [sheet1$] where col1='x'"

[GlennFromIowa]

@Vikky I'm not familiar with the VBA query you reference, but am intrigued by it. Do you have a link to documentation (MSDN or otherwise) for this type of query? Is this used when referencing Excel from another application or tool via OLE or ODBC? If so, which application?

[Vikky]

First i would like to thank you for replying. i have find solution for my question, tables within excel sheet can be used using oledb. this is the link of article which helps me chandoo.org/wp/2012/04/02/using-excel-as-your-database

[Pork]

Not a huge fan of your suggested VBA but the outside link you dropped was a nice summary of how to reference tables and parts of tables, thanks.

[Charles Wood]

"Tables in Excel make so much sense, but they're so hard to work with in VBA!" Tell me about it! Why can't they just be queried like a database table?

[ChrisB]

Given what @AndrewD wrote about the potential shortcomings of application.range method, this seems like the only reliable method for getting the ListObject of a table using only the table name.

[Egalth]

@Vikky, that link about how to use Excel for OLE DB was indeed interesting, but note that it is unrelated to the concept of a table as discussed here, so it's not correct to say that "tables within excel sheet can be used usin oledb". (From your link: "Before we begin: ... 2. This has nothing related with the in-built Table (2007 and greater) / List (2003 and previous) feature of Excel.")

[Egalth]

Upvote for hack and NOTE. In fact, I added a reference in a new question I just posted: stackoverflow.com/q/46058096/5457466 Maybe something you could help with, @AndrewD?

[GlennFromIowa]

Aside from creating the PivotTable in your code, which probably wasn't your intent, I think your way is as direct as it can be. I tried to add on a generic function to get a ListObject given a PivotTable name to your answer, but apparently that's not kosher. So I decided to create a new question and provide that code as an answer. But I did upvote your answer here, because that's a foreseeable need to be able to get a ListObject from the PivotTable Name.

[JeopardyTempest]

Love the hack. Because it'd seem the danger of a sheet name being changed is very real. Still wonder how it'd handle it if the user had an old save of the file open as well as the current one... if it would error out due to duplicate names. But tm's comment makes me think it might just work anyways so long as you run it from the workbook you want?

[JeopardyTempest]

(When uncertain... try it out. Looks indeed like it succeeds despite duplicate names existing in another open file.)

[JeopardyTempest]

Your unhappy caveat about the range somehow getting renamed from the table name aside, this is brilliant for use not just on tables, but all named ranges... it seems to be able to handle users inadvertantly renaming sheets... or a redesign where various name cells are remapped to different sheets... when a function is interacting with things on multiple sheets [or even if they're all originally on ActiveSheet, but may not be later], without needing VB edits. Just seems flexibility that (perhaps in my nonexpertise with VBA, I don't understand how to handle such changes otherwise?), seems vital