Iterate to Find Listobject in a Workbook

Question

I am trying to find a table ( ListObject ) from a excel workbook ( Lets say workbook 2 ) after opening the same through a VBA subroutine in another workbook ( Lets say workbook 1 ).

The code I tried is as follows ,

Sub B()
        Dim TBL_EMP As ListObject
        Dim strFile As Variant
        Dim WS_Count As Integer
        
        strFile = "File Path"
        Set WB_TRN = Workbooks.Open(strFile)
        
        WS_Count = WB_TRN.Worksheets.Count
        For n = 1 To WS_Count
                On Error GoTo Next_IT
                Set TBL_EMP = WB_TRN.Worksheets(n).ListObjects("EmployeeNameTbl")
                If Not TBL_EMP Is Nothing Then
                    MsgBox "Object Found"
                End If
    Next_IT:
        Next n
    End Sub

When I run the subroutine it iterate only through 2 sheets and gives error code 9 " ( Subscript Out of Range ) eventhough workbook 2 has 10 worksheets.

If I open the workbook 2 through file open dialogue box then the code works fine.

Please help me to solve this. Thank you in advance

That's not how error handling works in VBA. You should either iterate through the listobjects on the sheet and check each one's name, or use `On Error Resume Next` instead of your current line, with `On Error Goto 0` to reset. — Rory, Dec 13 '21 at 16:48

VBasic2008 · Accepted Answer · 2021-12-13T17:19:25.027

Referencing a Table in a Workbook

A 'Sub' Example

Sub LocateTableExample()
    
    Const FilePath As String = "C:\Test\Test.xlsx"
    Const TableName As String = "EmployeeNameTbl"
    
    Dim wb As Workbook: Set wb = Workbooks.Open(FilePath)
    'Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim tbl As ListObject
    Dim IsFound As Boolean
    
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        On Error Resume Next
            Set tbl = ws.ListObjects(TableName)
        On Error GoTo 0
        If Not tbl Is Nothing Then
            IsFound = True
            Exit For ' stop looping, it is found
        End If
    Next ws

    ' Continue using the 'tbl' and 'ws' variables.
    Debug.Print tbl.Name
    Debug.Print ws.Name

            
    If IsFound Then
        MsgBox "Table found in worksheet '" & ws.Name & "'.", vbInformation
    Else
        MsgBox "Table not found.", vbCritical
    End If
    
End Sub

Using a Function

The procedure ReferenceTableTest utilizes (calls) the following ReferenceTable function.

Sub ReferenceTableTest()
    
    Const FilePath As String = "C:\Test\Test.xlsx"
    Const TableName As String = "EmployeeNameTbl"
        
    Dim wb As Workbook: Set wb = Workbooks.Open(FilePath)
    'Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim tbl As ListObject: Set tbl = ReferenceTable(wb, TableName)
    If tbl Is Nothing Then Exit Sub
    
    Debug.Print "Get the Names Using the 'tbl' variable"
    Debug.Print "Table Name:     " & tbl.Name
    Debug.Print "Worksheet Name: " & tbl.Range.Worksheet.Name
    Debug.Print "Workbook Name:  " & tbl.Range.Worksheet.Parent.Name
    
End Sub

Function ReferenceTable( _
    ByVal wb As Workbook, _
    ByVal TableName As String) _
As ListObject
    Const ProcName As String = "ReferenceTable"
    On Error GoTo ClearError
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    
    For Each ws In wb.Worksheets
        On Error Resume Next
            Set tbl = ws.ListObjects(TableName)
        On Error GoTo 0
        If Not tbl Is Nothing Then
            Set ReferenceTable = tbl
            Exit For
        End If
    Next ws
    
ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "': Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "    " & Err.Description
    Resume ProcExit
End Function

Note that I just corrected a few minor mistakes mainly concerning the `TableName` constant. Sorry and thanks. — VBasic2008, Dec 13 '21 at 17:21

Iterate to Find Listobject in a Workbook

1 Answers1

Referencing a Table in a Workbook