checking if a sheet exists

  • Thread starter Thread starter greg
  • Start date Start date
G

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.
 
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.
 
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
 
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
 
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
 
Back
Top