迭代以在作業簿中查找Listobject

在通過另一個作業簿（假設作業簿 1）中的 VBA 子例程打開相同的表格后，我試圖從 excel 作業簿（假設作業簿 2）中找到一個表（ ListObject ）。

我試過的代碼如下，

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

當我運行子例程時，它只迭代 2 個作業表并給出錯誤代碼 9"（下標超出范圍），即使作業簿 2 有 10 個作業表。

如果我通過檔案打開對話框打開作業簿 2，則代碼作業正常。

請幫我解決這個問題。先感謝您

uj5u.com熱心網友回復：

參考作業簿中的表

“子”示例

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

使用函式

• 該程序ReferenceTableTest使用（呼叫）以下ReferenceTable函式。

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

標籤：擅长 vba

上一篇：需要遍歷列AI，如果單元格不為空，則查看列W并在下一列中移動數字

下一篇：用于366列左邊框格式的VBA“ForEach”代碼