This should do the job:


Function SheetExistsInClosedWB(strFolder As String, _
strFile As String, _
strSheet As String) As Long

'will return:
'-1 if file doesn't exist
'-2 if sheet doesn't exist
'0 if both file and sheet exist
'-------------------------------

Dim strSep As String
Dim strArgs As String
Dim strResult As String

On Error GoTo ERROROUT

strSep = "\"

If Right$(strFolder, 1) < strSep Then
strFolder = strFolder & strSep
End If

'see if the file exists
'----------------------
If bFileExists(strFolder & strFile) = False Then
SheetExistsInClosedWB = -1
Exit Function
End If

'create the argument
'-------------------
strArgs = "'" & strFolder & "[" & strFile & "]" & strSheet & "'!" & _
Range("A1").Address(, , xlR1C1)

'execute the Excel4 macro and test for the sheet
'----------------------------------------------
strResult = ExecuteExcel4Macro(strArgs)

Exit Function
ERROROUT:

SheetExistsInClosedWB = -2

End Function


Function bFileExists(strFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(strFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


Sub test()

MsgBox SheetExistsInClosedWB("C:\ExcelFiles", "AA13.xls", "Sheet1")

End Sub



RBS


"Jim" wrote in message
...
I need to find out if a worksheet named Sheet1 exists in a closed workbook.

A search of news groups suggest you can use ADO to find out sheet names in
closed workbooks but I don't need to know the names. I just need to know
if Sheet1 exists in a workbook. If it doesn't exist, that workbook is
ignored. I have other validation code to screen for improper data on
closed workbooks when the name is Sheet1 which is a pretty common name so
that part is not a problem.

My code for reading data from closed workbooks works fine until a workbook
is found that doesn't have a sheet named Sheet1. Then Excel puts up a
list of sheet names for the workbook. I haven't coded for this, don't
know how and don't want to and even though it creates an error eventually
that I handle, it thoroughly confuses Users.

I'd sure like to find some way to query the closed workbook to see if
Sheet1 exist. An error returned on a workbook without Sheet1 is fine
since that can be handled.

Ideas would be very greatly appreciated.