How to tell if sheet exists in closed workbook

[Jim]

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.

 

[RB Smissaert]

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]

Wow! This works great. I don't completely understand "strResult =
ExecuteExcel4Macro(strArgs)" part but have looked it up on the web and am
beginning to get the gist of it.

This is exactly what I need.

Thank you very much. I greatly appreciate it.

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

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.

 

[RB Smissaert]

No trouble; it was a little alteration to code I had to get values from a
closed workbook.

RBS

Wow! This works great. I don't completely understand "strResult =
ExecuteExcel4Macro(strArgs)" part but have looked it up on the web and am
beginning to get the gist of it.

This is exactly what I need.

Thank you very much. I greatly appreciate it.

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

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.

 

[RDarmanin]

Hi, not sure this message will get a reply given that this was solved years ago. I am trying to use this code to check if a sheet exists in a closed WB, as it looks great. However, it does not seem to be working well for me. The code does seem to correctly recognise that the file exists, but I am getting a '-2' even if the sheet exists. For clarity, this is what I am doing:

in a random cell: "=sheetexistsinclosedWB("V:\folderpath", "filename.xlsx", "SheetName")

I tried to add other characters, such as the usual [ and ' when linking, but I see that these are already catered for in the code. Any help would be appreciated. Thanks.