checking if a sheet exists

[greg]

Is there a good way of checking if a sheet exists?
Besides doing this
excel.ActiveWorkbook.Sheets("foo").name
and catching the error that is thrown.

 

[Dave Peterson]

I think that's the best way I've seen.

But if you don't like, you could loop through all the sheets to see if any of
the names match the name you're looking for. That seems less efficient than
what you suggest.

 

[Tom Ogilvy]

looping through the sheets and checking each name

 

[Gary Brown]

That is the best way as far as I can see....
'/===============================/
Public Function FileExists(strFileName As String) _
As Boolean
FileExists = False
If Dir(strFileName) <> "" Then
FileExists = True
End If
End Function
'/===============================/

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Gary Brown]

oops read it wrong. You said sheet, I thought file :O<
'==============================================
Public Function SheetExists(sName) As Boolean
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sName)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
'==============================================

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Charlie]

That's how I do it but you could do something like:

Public Function SheetExists(SheetName As String) As Boolean
'
Dim Sheet As Worksheet
'
For Each Sheet In ThisWorkbook.Worksheets
If Sheet.Name = SheetName Then
SheetExists = Yes
Exit Function
End If
Next Sheet
'
End Function

 

[Ron de Bruin]

There are a few ways on this page
http://www.rondebruin.nl/exist.htm