Find worksheet
 

I'm working with some code that opens a file and then needs to determine if a
specific worksheet exists in that file. How can I determine that? Thanks as
always!

Find worksheet
 

Try this

Sub test()
MsgBox "Sheet1 Esists? " & SheetExists("Sheet1")
MsgBox "asdf Esists? " & SheetExists("asdf")

End Sub


Public Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
Dim blnReturnValue As Boolean

blnReturnValue = False
For Each wks In Worksheets
If wks.Name = SheetName Then blnReturnValue = True
Next wks
SheetExists = blnReturnValue

End Function

HTH

"cottage6" wrote:

I'm working with some code that opens a file and then needs to determine if a
specific worksheet exists in that file. How can I determine that? Thanks as
always!

Find worksheet
 

Function WksExists(ByVal wkb As Workbook, _
ByVal strSheetName As String) As Boolean
Dim wks As Worksheet
WksExists = False
For Each wks In wkb.Worksheets
If UCase(wks.Name) = UCase(strSheetName) Then
WksExists = True
Exit For
End If
Next
End Function



"cottage6" wrote in message
...
I'm working with some code that opens a file and then needs to determine
if a
specific worksheet exists in that file. How can I determine that? Thanks
as
always!

Find worksheet
 

Jim..

I think you forgot a crucial exit for :)
as is it will only give a true if the search sheet is the last one..
also your search is case sensitive.. which may be unexpected

edited:

Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If StrComp(wks.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next wks
End Function

or alternative

Function WorkSheetExists(SheetName$, Optional wkb As Workbook) As
Boolean
On Error GoTo TheExit
If wkb Is Nothing Then Set wkb = ActiveWorkbook
WorkSheetExists = Not wkb.Worksheets(SheetName) Is Nothing
TheExit:
End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Thomlinson wrote :

Try this

Sub test()
MsgBox "Sheet1 Esists? " & SheetExists("Sheet1")
MsgBox "asdf Esists? " & SheetExists("asdf")

End Sub


Public Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If strComp(wks.Name,SheetName,vbCompareText) = 0 Then
sheetexists = True
exit for
end if
Next wks
End Function


HTH

"cottage6" wrote:

I'm working with some code that opens a file and then needs to
determine if a specific worksheet exists in that file. How can I
determine that? Thanks as always!

Find worksheet
 

The strcomp is a very good point. You are absolutely correct. I personally
never refer to sheet names in code since the user can change those... Darn
users... As for the exit for, it is not really crucial. Nice perhaps but how
many sheet will we be dealing with. The code will look through all of the
sheets so fast that it really will not matter to any significant degree...
The code will complete long before the screen has had the chance to refresh...

As for your second option I always avoid generating errors when I can
reasonably do so. Errors have way too much overhead and it is a bias of mine
errors should only be generated because something went horribly wrong and now
I need to react gracefully...

Thanks for the input...

"keepITcool" wrote:

Jim..

I think you forgot a crucial exit for :)
as is it will only give a true if the search sheet is the last one..
also your search is case sensitive.. which may be unexpected

edited:

Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If StrComp(wks.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next wks
End Function

or alternative

Function WorkSheetExists(SheetName$, Optional wkb As Workbook) As
Boolean
On Error GoTo TheExit
If wkb Is Nothing Then Set wkb = ActiveWorkbook
WorkSheetExists = Not wkb.Worksheets(SheetName) Is Nothing
TheExit:
End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Thomlinson wrote :

Try this

Sub test()
MsgBox "Sheet1 Esists? " & SheetExists("Sheet1")
MsgBox "asdf Esists? " & SheetExists("asdf")

End Sub


Public Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If strComp(wks.Name,SheetName,vbCompareText) = 0 Then
sheetexists = True
exit for
end if
Next wks
End Function


HTH

"cottage6" wrote:

I'm working with some code that opens a file and then needs to
determine if a specific worksheet exists in that file. How can I
determine that? Thanks as always!

Find worksheet
 

I set the blnReturnValue = False (not necessary as it would be initialized
that way but it makes the code a little more readable). I then only switch
the variable to true if I get a match at any point. It does not rely on the
last sheet, only the last value it was set too. I have a bias in functions
that I always set the returned value as the last line of code in the
function. It makes debugging a little easier in my opinion if you only set
the returned value once at the very end... My prof for C was adamant on the
point and I have never lost that style...

"keepITcool" wrote:

Jim..

I think you forgot a crucial exit for :)
as is it will only give a true if the search sheet is the last one..
also your search is case sensitive.. which may be unexpected

edited:

Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If StrComp(wks.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next wks
End Function

or alternative

Function WorkSheetExists(SheetName$, Optional wkb As Workbook) As
Boolean
On Error GoTo TheExit
If wkb Is Nothing Then Set wkb = ActiveWorkbook
WorkSheetExists = Not wkb.Worksheets(SheetName) Is Nothing
TheExit:
End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Thomlinson wrote :

Try this

Sub test()
MsgBox "Sheet1 Esists? " & SheetExists("Sheet1")
MsgBox "asdf Esists? " & SheetExists("asdf")

End Sub


Public Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If strComp(wks.Name,SheetName,vbCompareText) = 0 Then
sheetexists = True
exit for
end if
Next wks
End Function


HTH

"cottage6" wrote:

I'm working with some code that opens a file and then needs to
determine if a specific worksheet exists in that file. How can I
determine that? Thanks as always!

Find worksheet
 

Sorry Jim.

I was doing some comparisons and kept getting different results.
didn't look properly and blamed it on the missing exit for,
where in fact the problem was case sensitivity.

Both our problems would be solved it the collection object
had a ItemExists method ..

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Thomlinson wrote :

I set the blnReturnValue = False (not necessary as it would be
initialized that way but it makes the code a little more readable). I
then only switch the variable to true if I get a match at any point.
It does not rely on the last sheet, only the last value it was set
too. I have a bias in functions that I always set the returned value
as the last line of code in the function. It makes debugging a little
easier in my opinion if you only set the returned value once at the
very end... My prof for C was adamant on the point and I have never
lost that style...

"keepITcool" wrote:

Jim..

I think you forgot a crucial exit for :)
as is it will only give a true if the search sheet is the last one..
also your search is case sensitive.. which may be unexpected

edited:

Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If StrComp(wks.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next wks
End Function

or alternative

Function WorkSheetExists(SheetName$, Optional wkb As Workbook) As
Boolean
On Error GoTo TheExit
If wkb Is Nothing Then Set wkb = ActiveWorkbook
WorkSheetExists = Not wkb.Worksheets(SheetName) Is Nothing
TheExit:
End Function


--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Thomlinson wrote :

Try this

Sub test()
MsgBox "Sheet1 Esists? " & SheetExists("Sheet1")
MsgBox "asdf Esists? " & SheetExists("asdf")

End Sub


Public Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If strComp(wks.Name,SheetName,vbCompareText) = 0 Then
sheetexists = True
exit for
end if
Next wks
End Function


HTH

"cottage6" wrote:

I'm working with some code that opens a file and then needs to
determine if a specific worksheet exists in that file. How can
I determine that? Thanks as always!

Find worksheet
 

Thanks for all the responses! Exactly what I needed!

"cottage6" wrote:

I'm working with some code that opens a file and then needs to determine if a
specific worksheet exists in that file. How can I determine that? Thanks as
always!

Find worksheet
 

You are preaching to the quior brother... Amen to that...

P.S. I like your code...

"keepITcool" wrote:

Sorry Jim.

I was doing some comparisons and kept getting different results.
didn't look properly and blamed it on the missing exit for,
where in fact the problem was case sensitivity.

Both our problems would be solved it the collection object
had a ItemExists method ..

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Thomlinson wrote :

I set the blnReturnValue = False (not necessary as it would be
initialized that way but it makes the code a little more readable). I
then only switch the variable to true if I get a match at any point.
It does not rely on the last sheet, only the last value it was set
too. I have a bias in functions that I always set the returned value
as the last line of code in the function. It makes debugging a little
easier in my opinion if you only set the returned value once at the
very end... My prof for C was adamant on the point and I have never
lost that style...

"keepITcool" wrote:

Jim..

I think you forgot a crucial exit for :)
as is it will only give a true if the search sheet is the last one..
also your search is case sensitive.. which may be unexpected

edited:

Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If StrComp(wks.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next wks
End Function

or alternative

Function WorkSheetExists(SheetName$, Optional wkb As Workbook) As
Boolean
On Error GoTo TheExit
If wkb Is Nothing Then Set wkb = ActiveWorkbook
WorkSheetExists = Not wkb.Worksheets(SheetName) Is Nothing
TheExit:
End Function


--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Thomlinson wrote :

Try this

Sub test()
MsgBox "Sheet1 Esists? " & SheetExists("Sheet1")
MsgBox "asdf Esists? " & SheetExists("asdf")

End Sub


Public Function SheetExists(ByVal SheetName As String) As Boolean
Dim wks As Worksheet
For Each wks In Worksheets
If strComp(wks.Name,SheetName,vbCompareText) = 0 Then
sheetexists = True
exit for
end if
Next wks
End Function


HTH

"cottage6" wrote:

I'm working with some code that opens a file and then needs to
determine if a specific worksheet exists in that file. How can
I determine that? Thanks as always!