Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find and unlink current worksheet from old worksheet | Excel Discussion (Misc queries) | |||
Find worksheet | Excel Discussion (Misc queries) | |||
Find Max Value in WorkSheet | Excel Worksheet Functions | |||
Find a worksheet | Excel Programming | |||
find last row on a worksheet | Excel Programming |