Excel VBA - 如何检查不同工作簿中是否存在表格？

Excel VBA - How to check for presence of a table in a different workbook?

提问by AdamDynamic

I am trying to check for the presence of named ranges in workbooks as a macro I have written iterates through a folder full of files. The macro I have written below works for 'normal' named ranges but fails when I pass the function a table name as a named range.

我正在尝试检查工作簿中是否存在命名范围，因为我编写的宏迭代了一个充满文件的文件夹。我在下面编写的宏适用于“正常”命名范围，但当我将表名作为命名范围传递给函数时失败。

I've searched here and elsewhere on the internet as to why this might be the case, can anyone point out what I'm (probably obviously) doing wrong?

我已经在这里和互联网上的其他地方搜索过为什么会这样，谁能指出我（可能很明显）做错了什么？

Any help would be appreciated!

任何帮助，将不胜感激！

Thanks,

谢谢，

Adam

亚当

Public Function DoesNamedRangeExistInWorkbook(ByVal wb As Workbook, ByVal rangeName As String) As Boolean

Const FN_NAME As String = "DoesNamedRangeExistInWorkbook"
On Error GoTo catch

    Dim rng As Range
    Dim cellCount As Integer

    Set rng = wb.Names(rangeName).RefersToRange
    cellCount = rng.Cells.Count
    DoesNamedRangeExistInWorkbook = True

finally:
    Set rng = Nothing
    Exit Function

catch:
    Call ErrorReport(FN_NAME, False, Err.Number, Err.Description, rangeName & " could not be found in workbook: " & wb.Name)
    DoesNamedRangeExistInWorkbook = False
    Resume finally

End Function

采纳答案by Siddharth Rout

Try this (UNTESTED)

试试这个（未经测试）

I am assuming that the table name is also the named range for that table else you will have to loop through the table names to check for it. Let me know if that is the case and I will update the code for that.

我假设表名也是该表的命名范围，否则您将不得不遍历表名来检查它。如果是这种情况，请告诉我，我会为此更新代码。

Public Function DoesNamedRangeExistInWorkbook(ByVal wb As Workbook, _
ByVal rangeName As String) As Boolean
    On Error GoTo catch

    Dim rng As Range

    On Error Resume Next
    Set rng = wb.Names(rangeName)
    On Error GoTo 0

    If Not rng is Nothing Then
        DoesNamedRangeExistInWorkbook = True
        Set rng = Nothing
    Else
        DoesNamedRangeExistInWorkbook = False
    End If

    Exit Function
catch:
    DoesNamedRangeExistInWorkbook = False
End Function

EDIT

编辑

Here is the code for checking if a particular table exists in a workbook or not. We are using the table name to check it's existence. Again this code is untested.

这是用于检查工作簿中是否存在特定表的代码。我们正在使用表名来检查它的存在。同样，此代码未经测试。

Public Function DoesTableExist(ByVal wb As Workbook, _
ByVal tblName As String) As Boolean
    On Error GoTo catch

    DoesTableExist = False

    Dim lstobj As ListObject, ws As Worksheet

    For Each ws In wb.Worksheets
        For Each lstobj In ws.ListObjects
            If lstobj.Name = tblName Then
                DoesTableExist = True
                Exit Function
            End If
        Next
    Next

    Exit Function
catch:
    DoesTableExist = False
End Function