Visual Basic, function to see if an Excel worksheet name exitsts in a workbook

function to see if an Excel worksheet name exitsts in a workbook

Author	Message
Joh #1 / 5	function to see if an Excel worksheet name exitsts in a workbook Hi, I'm trying to write a macro, part of which looks for a specific worksheet name in a workbook. If the worksheet exists, I've got no problem, I simply use: sheets."name".select (where "name" is the text name I'm looking for). My question is this: how do I write an if statement to tell it what to do if the worksheet is not present? I've been playing with variations of: If find.sheets."name" = false then but that doesn't work. Can anyone help me out? Thanks. John
Tue, 06 Jul 2004 10:42:07 GMT

N Lawto #2 / 5	function to see if an Excel worksheet name exitsts in a workbook Can't you just use the same code (that you know works) but trap the error and exit gracefully if the sheet does not exist? Quote: > Hi, I'm trying to write a macro, part of which looks for a specific > worksheet name in a workbook. If the worksheet exists, I've got no > problem, I simply use: > sheets."name".select (where "name" is the text name I'm looking > for). > My question is this: how do I write an if statement to tell it what to > do if the worksheet is not present? I've been playing with variations > of: > If find.sheets."name" = false then > but that doesn't work. Can anyone help me out? Thanks. > John
Tue, 06 Jul 2004 14:21:40 GMT

Joh #3 / 5	function to see if an Excel worksheet name exitsts in a workbook I'm not sure what "trapping the error" means, but I can't just exit at that point because I need the routine to look for other sheets (from a list in a different file) to see if they exist, and if they do, it retrives certain info. Problem is that if the sheet is not found, I don't want it to crash, I need it to simply keep looking for the others, but don't know how to tell the program to move on if some sheets are not found. John Quote: > Can't you just use the same code (that you know works) but trap the error > and exit gracefully if the sheet does not exist? > > Hi, I'm trying to write a macro, part of which looks for a specific > > worksheet name in a workbook. If the worksheet exists, I've got no > > problem, I simply use: > > sheets."name".select (where "name" is the text name I'm looking > > for). > > My question is this: how do I write an if statement to tell it what to > > do if the worksheet is not present? I've been playing with variations > > of: > > If find.sheets."name" = false then > > but that doesn't work. Can anyone help me out? Thanks. > > John
Wed, 07 Jul 2004 02:37:17 GMT

N Lawto #4 / 5	function to see if an Excel worksheet name exitsts in a workbook The following code should continue to process a second sheet OK if the first is not found. If you are running this from VB Worksheets may need to be prequalified with an Excel.Application Object. On Error Resume Next 'Process the first sheet With Worksheets("FirstSheet") If Err.Number = 0 Then 'Do what ever processing you want here Else Err.Clear End if End With 'Process the next sheet With Worksheets("SecondSheet") If Err.Number = 0 Then 'Process content here Else Err.Clear End if End with 'Etc Etc Etc Quote: > I'm not sure what "trapping the error" means, but I can't just exit at > that point because I need the routine to look for other sheets (from a > list in a different file) to see if they exist, and if they do, it > retrives certain info. Problem is that if the sheet is not found, I > don't want it to crash, I need it to simply keep looking for the > others, but don't know how to tell the program to move on if some > sheets are not found. > John Quote: > > Can't you just use the same code (that you know works) but trap the error > > and exit gracefully if the sheet does not exist? > > > Hi, I'm trying to write a macro, part of which looks for a specific > > > worksheet name in a workbook. If the worksheet exists, I've got no > > > problem, I simply use: > > > sheets."name".select (where "name" is the text name I'm looking > > > for). > > > My question is this: how do I write an if statement to tell it what to > > > do if the worksheet is not present? I've been playing with variations > > > of: > > > If find.sheets."name" = false then > > > but that doesn't work. Can anyone help me out? Thanks. > > > John
Thu, 08 Jul 2004 07:41:42 GMT

James Can #5 / 5	function to see if an Excel worksheet name exitsts in a workbook Slightly slower, but arguably tidier: Private Function WorksheetExists (oWorkbook As Workbook, sSheetName As String) As Boolean Dim oLoopSheet As Worksheet For Each oLoopSheet In oMyWorkbook.Worksheets If oLoopSheet.Name = sSheetName Then WorksheetExists = True Exit For End If Next End Function If you want to search for charts as well as worksheets, you should use the Sheets collection and change the looping variable to Object. Quote: > The following code should continue to process a second sheet OK if the first > is not found. > If you are running this from VB Worksheets may need to be prequalified with > an Excel.Application Object. > On Error Resume Next > 'Process the first sheet > With Worksheets("FirstSheet") > If Err.Number = 0 Then > 'Do what ever processing you want here > Else > Err.Clear > End if > End With > 'Process the next sheet > With Worksheets("SecondSheet") > If Err.Number = 0 Then > 'Process content here > Else > Err.Clear > End if > End with > 'Etc Etc Etc > > I'm not sure what "trapping the error" means, but I can't just exit at > > that point because I need the routine to look for other sheets (from a > > list in a different file) to see if they exist, and if they do, it > > retrives certain info. Problem is that if the sheet is not found, I > > don't want it to crash, I need it to simply keep looking for the > > others, but don't know how to tell the program to move on if some > > sheets are not found. > > John > > > Can't you just use the same code (that you know works) but trap the > error > > > and exit gracefully if the sheet does not exist? > > > > Hi, I'm trying to write a macro, part of which looks for a specific > > > > worksheet name in a workbook. If the worksheet exists, I've got no > > > > problem, I simply use: > > > > sheets."name".select (where "name" is the text name I'm looking > > > > for). > > > > My question is this: how do I write an if statement to tell it what to > > > > do if the worksheet is not present? I've been playing with variations > > > > of: > > > > If find.sheets."name" = false then > > > > but that doesn't work. Can anyone help me out? Thanks. > > > > John
Thu, 08 Jul 2004 08:43:37 GMT

Page 1 of 1

[ 5 post ]