Check if Sheet Exists in Closed Workbook-CodePudding

I'm working with code posted in the following post:

Checking If A Sheet Exists In An External Closed Workbook

My Code is:

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 different filenames that containing the sheet I'm checking for or not to the function the result is always the same:

HasSheet = False / Err.Number = 0

Can anyone help/see where I am going wrong?

Many Thanks in Advance!

CodePudding user response：

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

CodePudding user response：

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!

CodePudding user response：

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