CODEWITHSUNDEEP
vbaexcelfile-io
packy
packy

Reputation: 23

Visual Basic, Check if a sheet exists in another workbook

I'm really new to Visual Basic and I don't know any python either, I'm trying to write code that is able to check if a worksheet exists in a workbook...

Sub sheetexist()
    If Len(Dir(("C:\My Data\Performance Spreadsheets\[ABCD - Performance.xls]Jun 14"))) Then
        MsgBox "Sheet exist"
    Else
        MsgBox "Sheet does not exist"
    End If
End Sub

ABCD does have the sheet for Jun 14 however the code only returns "Sheet does not exist", is there another way to check for worksheets in other workbooks?

Upvotes: 2

Views: 8179

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I think you're mis-using the Dir function.

The easiest way to check if a sheet exists is with error-handling. 

Function SheetExists(wbPath as String, shName as String)
    Dim wb as Workbook
    Dim val

    'Assumes the workbook is NOT open
    Set wb = Workbooks.Open(wbPath)
    On Error Resume Next
    val = wb.Worksheets(shName).Range("A1").Value
    SheetExists = (Err = 0)

    'Close the workbook
    wb.Close

End Function

Call the function like this from a worksheet cell:

=SheetExists("C:\My Data\Performance Spreadsheets\ABCD - Performance.xls", "Jun 14")

Or from VBA like:

Debug.Print SheetExists("C:\My Data\Performance Spreadsheets\ABCD - Performance.xls", "Jun 14")

Without opening the workbook, you could use the code here.

This will raise an error if any part of the formula can't evaluate (e.g., if you pass the name of a non-existent sheet, a bad file path, etc., Error 2023: 

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

Call it:

Sub Test()
Dim path As String
Dim filename As String
Dim sheetName As String
Dim cellAddress As String

path = "c:\users\you\desktop"

filename = "file.xlsx"

sheetName = "Jun 14"

cellAddress = "A1"

Dim v As Variant  'MUST BE VARIANT SO IT CAN CONTAIN AN ERROR VALUE

v = GetInfoFromClosedFile(path, filename, sheetName, cellAddress)
If IsError(v) Then MsgBox "Sheet or filename doesn't exist!"


End Sub

Upvotes: 4

Related Questions