Можно такой функцией спросить

1. В обычный модуль.
2. Первый аргумент FileName - путь и имя файла-книги с расширением
3. SheetName  - имя проверяемого на существование рабочего листа книги.
4. Возвращает True, если рабочий лист с таким именем в книге есть, иначе - False.

Public Function ExistsSheet(ByVal FileName As String, ByVal SheetName As String) As Boolean

    Dim pConn As Object

    Dim pRset As Object

    Set pConn = CreateObject("ADODB.Connection")

    pConn.CursorLocation = 3

    pConn.Open "DBQ=" & FileName & ";Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=1;"

    Set pRset = pConn.OpenSchema(20)

    pRset.Filter = "TABLE_NAME = '" & SheetName & "$'"

    ExistsSheet = pRset.RecordCount > 0

    pRset.Close: pConn.Close

    Set pRset = Nothing: Set pConn = Nothing

End Function

Еще вариант:
Перехватить ошибку и получить значение любой ячейки из требуемого листа закрытой книги.
Если получилось - значит лист существует, иначе - не существует.

Public Function ExistsSheet(ByVal p As String, ByVal f As String, s As String) As Boolean

    Dim arg As String, q

    arg = "'" & p & "\[" & f & "]" & s & "'!" & Range("A1").Range("A1").Address(, , xlR1C1)

    On Error Resume Next

    q = ExecuteExcel4Macro(arg)

    If Err = 0 Then ExistsSheet = True Else ExistsSheet = False

    On Error GoTo 0

End Function

Можно обойтись без тяжелой артиллерии! :)

Только я бы заменил 3 строку на

arg = "'" & p & "\[" & f & "]" & s & "'!R1C1"

, т.к. адрес ячейки А1 и так известен, а выражение Range("A1").Range("A1").Address(, , xlR1C1) вызывает ошибку, если activesheet является листом-диаграммой.