How to use VBA to check if sheet within an external file exists - Using Excel 2010

[cwunderlich]

I am creating VBA code. I need to check to see if a certain sheet exists within an external file. I would like to do this without opening up the external file.

I am currently using this code:

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

However, this does not seem to always work. I am testing this on a workbook which I know has the the existing sheet name, but the ExecuteExcel4Macro(arg) is not returning anything.

Any ideas?

 

[cwunderlich]

It looks like it *might* be related to the scenario when the external workbook only has one sheet in it??? I am not sure though..