Check if Sheet Exists in Closed Workbook

Question

I'm working with code from Checking If A Sheet Exists In An External Closed Workbook

Function HasSheet(fPath As String, fName As String, sheetName As String)
On Error Resume Next
Dim f As String

f = "'" & fPath & "[" & fName & "]" & sheetName & "'!R1C1"
Debug.Print f

HasSheet = Not IsError(Application.ExecuteExcel4Macro(f))
If Err.Number <> 0 Then
    HasSheet = False
End If
Debug.Print Err.Number
On Error GoTo 0
End Function

The variable f constructed from variables passed to the functions looks like this:

C:\Users\MyName\MyFolder[MyFile.xlsx]MySheet'!R1C1

When I pass filenames, containing the sheet I'm checking for, to the function the result is always the same:

HasSheet = False / Err.Number = 0

No error should be raised in any circumstances, I am afraid. The above function should only contain three code lines. The ones trying to catch an error are useless, `IsError(` suppressing everything, from this point of view. The result you receive only means that the used parameters (the real ones) are wrong, or no sheet in the closed workbook match the sheet name you test. — FaneDuru, Apr 08 '22 at 12:52
Now, I looked to the link you posted. The correct solution is two answers down. Anyhow, it has been marked as **accepted answer**... — FaneDuru, Apr 08 '22 at 12:57
There is probably a `\\` missing at the end of the fPath, 'C:\Users\MyName\MyFolder[MyFile.xlsx]MySheet'!R1C1 should be 'C:\Users\MyName\MyFolder\\[MyFile.xlsx]MySheet'!R1C1 — Vincent G, Apr 08 '22 at 13:04
[The correct code](https://stackoverflow.com/a/37824349) by Tim Williams should you be using and upvoting. Only three lines. — VBasic2008, Apr 08 '22 at 13:06
@Vincent G `fPath` should contain it. So it is built in the linked page, but he missed the right answer... — FaneDuru, Apr 08 '22 at 13:10
Thanks for the input all - I changed to the right code and resolved the missing "\" — AndyC, Apr 08 '22 at 21:01

score 0 · Answer 1 · answered Apr 08 '22 at 22:27

combine best answers:

Open Excel file for reading with VBA without display
Check-if-sheet-exists

sub SheetExistsInClosedWorkbook()
    Dim app as New Excel.Application
    app.Visible = False 'Visible is False by default, so this isn't necessary
    Dim book As Excel.Workbook
    Set book = app.Workbooks.Add(fileName)
    Dim SheetExists As Boolean
    SheetExists= WorksheetExists("somesheet", "someWb" )
    book.Close SaveChanges:=False
    app.Quit
    Set app = Nothing
if SheetExists then
   msgbox "somesheet" & " exists in " & "someWb"
   else
   msgbox "somesheet" & " does not Exist in " & "someWb"  
End if
End sub

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet
    If wb Is Nothing Then Set wb = ActiveWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

score 0 · Answer 2 · answered Apr 09 '22 at 12:54

I went with the original code with the corrections to my syntax mentioned above:

Function HasSheet(fPath As String, fName As String, sheetName As String)

    Dim f As String

    f = "'" & fPath & "[" & fName & "]" & sheetName & "'!R1C1"

    HasSheet = Not IsError(Application.ExecuteExcel4Macro(f))

End Function

Thanks for all the help!

score 0 · Answer 3 · answered Apr 09 '22 at 18:06

Another method from:

excelchamps:Check-sheet-exists

Sub vba_check_sheet()

Dim wb As Workbook
Dim sht As Worksheet
Dim shtName As String

shtName = InputBox(Prompt:="Enter the sheet name", _
Title:="Search Sheet")

Application.ScreenUpdating = False

Set wb = Workbooks.Open _
("C:\Users\Dell\Desktop\sample-file.xlsx")

For Each sht In wb.Worksheets

    If sht.Name = shtName Then

        wb.Close SaveChanges:=True
        MsgBox "Yes! " & shtName & " is there in the workbook." _
        , vbInformation, "Found"
        Exit Sub

    End If

Next sht

Application.ScreenUpdating = true

MsgBox "No! " & shtName _
& " is not there in the workbook.", _
vbCritical, "Not Found"

End Sub

Check if Sheet Exists in Closed Workbook

3 Answers3

Linked