CODEWITHSUNDEEP
excelvba
AdamDynamic
AdamDynamic

Reputation: 791

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

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

Upvotes: 3

Views: 9843

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 3

Related Questions