Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
What is the best way to check whether a given worksheet exists? For example, I would like to know whether the "Week 1" worksheet exists without throwing an error to the user. Thanks in advance for your help. -- Raman325 ------------------------------------------------------------------------ Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748 View this thread: http://www.excelforum.com/showthread...hreadid=397253 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
use error supression like this: Code ------------------- Function IsWorksheetPresent(Name As String) As Boolean Dim Ws As Worksheet On Error Resume Next For Each Wb In ThisWorkbook.Worksheets Err.Clear If UCase(Ws.Name) = Name Then On Error GoTo 0 IsWorksheetPresent = True Exit Function End If Next On Error GoTo 0 End Functio ------------------- this will return a True if the worksheet is present you could use it like this: Code ------------------- if IsWorksheetPresent("Week 1") then 'Do your Stuff end i ------------------- -- Dnere ----------------------------------------------------------------------- Dnereb's Profile: http://www.excelforum.com/member.php...fo&userid=2618 View this thread: http://www.excelforum.com/showthread.php?threadid=39725 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
Great, thanks for your help -- Raman32 ----------------------------------------------------------------------- Raman325's Profile: http://www.excelforum.com/member.php...fo&userid=2474 View this thread: http://www.excelforum.com/showthread.php?threadid=39725 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
Hi Raman325,
Try: Function SheetExists(sName As String, _ Optional ByVal wb As Workbook) As Boolean On Error Resume Next If wb Is Nothing Then Set wb = ActiveWorkbook SheetExists = CBool(Len(sheets(sName).Name)) End Function --- Regards, Norman "Raman325" wrote in message ... What is the best way to check whether a given worksheet exists? For example, I would like to know whether the "Week 1" worksheet exists without throwing an error to the user. Thanks in advance for your help. -- Raman325 ------------------------------------------------------------------------ Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748 View this thread: http://www.excelforum.com/showthread...hreadid=397253 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
"Raman325" wrote in
message ... What is the best way to check whether a given worksheet exists? For example, I would like to know whether the "Week 1" worksheet exists without throwing an error to the user. Thanks in advance for your help. Here's a function you can use to check for this. It assumes you're looking for a sheet in the currently active workbook. Function bSheetExists(ByRef szSheetName As String) As Boolean On Error Resume Next bSheetExists = Not (ActiveWorkbook.Sheets(szSheetName) Is Nothing) End Function -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
Does that code function correctly... I like the optional workbook argument
but I think you need to change the line. SheetExists = CBool(Len(sheets(sName).Name)) to SheetExists = CBool(Len(wb.sheets(sName).Name)) Otherwise this function will look at the active workbook won't it? -- HTH... Jim Thomlinson "Norman Jones" wrote: Hi Raman325, Try: Function SheetExists(sName As String, _ Optional ByVal wb As Workbook) As Boolean On Error Resume Next If wb Is Nothing Then Set wb = ActiveWorkbook SheetExists = CBool(Len(sheets(sName).Name)) End Function --- Regards, Norman "Raman325" wrote in message ... What is the best way to check whether a given worksheet exists? For example, I would like to know whether the "Week 1" worksheet exists without throwing an error to the user. Thanks in advance for your help. -- Raman325 ------------------------------------------------------------------------ Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748 View this thread: http://www.excelforum.com/showthread...hreadid=397253 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
Hi Jim,
Thank you. I added the workbook argument but omitted the intended qualification. As you correctly indicate, without the qualification, the function operates on the active workbook. --- Regards, Norman "Jim Thomlinson" wrote in message ... Does that code function correctly... I like the optional workbook argument but I think you need to change the line. SheetExists = CBool(Len(sheets(sName).Name)) to SheetExists = CBool(Len(wb.sheets(sName).Name)) Otherwise this function will look at the active workbook won't it? -- HTH... Jim Thomlinson "Norman Jones" wrote: Hi Raman325, Try: Function SheetExists(sName As String, _ Optional ByVal wb As Workbook) As Boolean On Error Resume Next If wb Is Nothing Then Set wb = ActiveWorkbook SheetExists = CBool(Len(sheets(sName).Name)) End Function --- Regards, Norman "Raman325" wrote in message ... What is the best way to check whether a given worksheet exists? For example, I would like to know whether the "Week 1" worksheet exists without throwing an error to the user. Thanks in advance for your help. -- Raman325 ------------------------------------------------------------------------ Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748 View this thread: http://www.excelforum.com/showthread...hreadid=397253 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see if a worksheet exists
No... thank you. The workbook argument is a nice touch that I am including in
my code archive. I have not had the need for it yet but I can shee where it will come in handy. -- HTH... Jim Thomlinson "Norman Jones" wrote: Hi Jim, Thank you. I added the workbook argument but omitted the intended qualification. As you correctly indicate, without the qualification, the function operates on the active workbook. --- Regards, Norman "Jim Thomlinson" wrote in message ... Does that code function correctly... I like the optional workbook argument but I think you need to change the line. SheetExists = CBool(Len(sheets(sName).Name)) to SheetExists = CBool(Len(wb.sheets(sName).Name)) Otherwise this function will look at the active workbook won't it? -- HTH... Jim Thomlinson "Norman Jones" wrote: Hi Raman325, Try: Function SheetExists(sName As String, _ Optional ByVal wb As Workbook) As Boolean On Error Resume Next If wb Is Nothing Then Set wb = ActiveWorkbook SheetExists = CBool(Len(sheets(sName).Name)) End Function --- Regards, Norman "Raman325" wrote in message ... What is the best way to check whether a given worksheet exists? For example, I would like to know whether the "Week 1" worksheet exists without throwing an error to the user. Thanks in advance for your help. -- Raman325 ------------------------------------------------------------------------ Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748 View this thread: http://www.excelforum.com/showthread...hreadid=397253 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking to see if a file exists. | Excel Discussion (Misc queries) | |||
Checking if Sheet Exists? | Excel Discussion (Misc queries) | |||
Checking if a worksheet already exists... | Excel Programming | |||
Checking to see if Folder exists | Excel Programming | |||
Checking 2 c if workbook exists | Excel Programming |