Visual Basic, Check if a sheet exists in another workbook

Question

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?

I think you're mis-using the `Dir` function here. That can be used to check the validity of a file or folder *path*. Is the workbook "ABCD - Performanx.xls" already open, or are you trying to find out if the sheet exists in a *closed* workbook? — David Zemens, Jul 23 '14 at 16:50
You need to open the workbook to check if a sheet exists in that workbook. `Dir()` doesn't handle what you're attempting. See: http://stackoverflow.com/questions/6688131/excel-vba-how-to-test-if-sheet-exists/6688482#6688482 — Tim Williams, Jul 23 '14 at 16:52
Is there a way to check if a sheet exists in closed workbooks? That is what I'm trying to accomplish... — packy, Jul 23 '14 at 16:56
Yes, see code in my updated answer below. It's possible to use the `ExecuteExcel4Macro` method to query a closed workbook. — David Zemens, Jul 23 '14 at 17:17

David Zemens · Accepted Answer · 2014-07-23T17:13:41.497

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

Visual Basic, Check if a sheet exists in another workbook

1 Answers1

Linked