Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check to see if a sheet with a particular name exists?
Hi,
VBA newbie here...I have written below code which calls out a sub and func. Problem is that when the sub procedure is called, which in turn invokes the function, I am unable to get the correct output. I want to check to see if the opened file has a worksheet named Impedance and if not, produce a message saying so...but problem is that I always get "This is not TTM Layer stackup file" message. What am I doing wrong? Thanks for help. Here's the code snippet. Private Sub cmdbut_Click() 'Make sure that either TTM or DDI is selected If ttm.Value = False And ddi.Value = False Then MsgBox "You must select the file type before proceeding", , "File Not Selected" Exit Sub Else If ttm.Value = True Then 'opening ttm file ttmfn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _ Title:="Please select a file") If ttmfn = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else MsgBox ttmfn, , "File Name" Application.Visible = False Workbooks.Open (ttmfn) End If Workbooks(1).Activate 'Call DoesSheetExist 'Worksheets("Impedance").Activate Call WorksheetCheck(ttmfn) End If End If End Sub 'SUB: Sub WorksheetCheck(ttmfn) Workbooks(1).Activate If SheetExists("Impedance") = True Then MsgBox "Click OK to Continue", vbOKCancel, "Continue" Else MsgBox "This is not TTM layerstackup file", , "Wrong File" End If End Sub 'FUNCTION: Function SheetExists(Impedance As String) As Boolean Dim sheetcount As Integer Dim t As Integer SheetExists = False sheetcount = ActiveWorkbook.Sheets.Count For t = 1 To sheetcount If Sheets(t).Name = "Impedance" Then SheetExists = True Exit Function End If Next t End Function |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check to see if a sheet with a particular name exists?
This line:
Call WorksheetCheck(ttmfn) is passing the name of the file--including the drive, path and filename. It's not checking for the existence of a worksheet. And it's always scary to me to use workbooks(1). How do you know to use the first workbook in the collection. And comparing strings could lead to an error, too. Impedance and impedance will not be the equal. And you were a little aggressive when you modified that SheetExists function. Those are variables that represent the names/objects you're passing to the function. You don't need to use the name of the sheet (Impedance) as the name of the variable. I'd try: Option Explicit Private Sub cmdbut_Click() Dim ttmfn As Variant Dim ttmfnWkbk As Workbook 'Make sure that either TTM or DDI is selected If ttm.Value = False _ And ddi.Value = False Then MsgBox prompt:="You must select the file type before proceeding", _ Title:="File Not Selected" Exit Sub Else If ttm.Value = True Then 'opening ttm file ttmfn = Application.GetOpenFilename _ (FileFilter:="Excel Files, *.xls", _ Title:="Please select a file") If ttmfn = False Then MsgBox "Stopping because you did not select a file" Exit Sub End If 'MsgBox ttmfn, , "File Name" 'I'm not sure why you wanted this. 'maybe you meant application.displayalerts = false 'or even application.screenupdating = false 'or even application.enableevents = false 'or any/all of the three. 'Application.Visible = False Set ttmfnWkbk = Workbooks.Open(ttmfn) If SheetExists(SheetName:="impedance", _ WhichBook:=ttmfnWkbk) = False Then MsgBox "Doesn't exist" Else MsgBox "rest of code here" End If End If End If End Sub Function SheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) End Function Varun wrote: Hi, VBA newbie here...I have written below code which calls out a sub and func. Problem is that when the sub procedure is called, which in turn invokes the function, I am unable to get the correct output. I want to check to see if the opened file has a worksheet named Impedance and if not, produce a message saying so...but problem is that I always get "This is not TTM Layer stackup file" message. What am I doing wrong? Thanks for help. Here's the code snippet. Private Sub cmdbut_Click() 'Make sure that either TTM or DDI is selected If ttm.Value = False And ddi.Value = False Then MsgBox "You must select the file type before proceeding", , "File Not Selected" Exit Sub Else If ttm.Value = True Then 'opening ttm file ttmfn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _ Title:="Please select a file") If ttmfn = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else MsgBox ttmfn, , "File Name" Application.Visible = False Workbooks.Open (ttmfn) End If Workbooks(1).Activate 'Call DoesSheetExist 'Worksheets("Impedance").Activate Call WorksheetCheck(ttmfn) End If End If End Sub 'SUB: Sub WorksheetCheck(ttmfn) Workbooks(1).Activate If SheetExists("Impedance") = True Then MsgBox "Click OK to Continue", vbOKCancel, "Continue" Else MsgBox "This is not TTM layerstackup file", , "Wrong File" End If End Sub 'FUNCTION: Function SheetExists(Impedance As String) As Boolean Dim sheetcount As Integer Dim t As Integer SheetExists = False sheetcount = ActiveWorkbook.Sheets.Count For t = 1 To sheetcount If Sheets(t).Name = "Impedance" Then SheetExists = True Exit Function End If Next t End Function -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check to see if a sheet with a particular name exists?
Dave,
Thanks a lot. It works. I have a few questions. They are probably dumb since I am very new to VBA and programming in general so please bear with me if you don't mind. 1) What's this line doing in the Function SheetExists: Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) 2) Likewise, can you please explain me the meaning of following line: SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) 3) I do not want the workbook selected to be opened hence I had Application.Visible = False followed by Workbooks(1).Active. Question is, how can I get around the workbook not being launched (i.e. excel opening the selected file) yet being available for being parsed in the background? Would "activating" the ttmfnWkBk work? If yes, can please show me how i.e. what's the command to activate the workbook so that it can be parsed later? Thanks again for help. "Dave Peterson" wrote: This line: Call WorksheetCheck(ttmfn) is passing the name of the file--including the drive, path and filename. It's not checking for the existence of a worksheet. And it's always scary to me to use workbooks(1). How do you know to use the first workbook in the collection. And comparing strings could lead to an error, too. Impedance and impedance will not be the equal. And you were a little aggressive when you modified that SheetExists function. Those are variables that represent the names/objects you're passing to the function. You don't need to use the name of the sheet (Impedance) as the name of the variable. I'd try: Option Explicit Private Sub cmdbut_Click() Dim ttmfn As Variant Dim ttmfnWkbk As Workbook 'Make sure that either TTM or DDI is selected If ttm.Value = False _ And ddi.Value = False Then MsgBox prompt:="You must select the file type before proceeding", _ Title:="File Not Selected" Exit Sub Else If ttm.Value = True Then 'opening ttm file ttmfn = Application.GetOpenFilename _ (FileFilter:="Excel Files, *.xls", _ Title:="Please select a file") If ttmfn = False Then MsgBox "Stopping because you did not select a file" Exit Sub End If 'MsgBox ttmfn, , "File Name" 'I'm not sure why you wanted this. 'maybe you meant application.displayalerts = false 'or even application.screenupdating = false 'or even application.enableevents = false 'or any/all of the three. 'Application.Visible = False Set ttmfnWkbk = Workbooks.Open(ttmfn) If SheetExists(SheetName:="impedance", _ WhichBook:=ttmfnWkbk) = False Then MsgBox "Doesn't exist" Else MsgBox "rest of code here" End If End If End If End Sub Function SheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) End Function Varun wrote: Hi, VBA newbie here...I have written below code which calls out a sub and func. Problem is that when the sub procedure is called, which in turn invokes the function, I am unable to get the correct output. I want to check to see if the opened file has a worksheet named Impedance and if not, produce a message saying so...but problem is that I always get "This is not TTM Layer stackup file" message. What am I doing wrong? Thanks for help. Here's the code snippet. Private Sub cmdbut_Click() 'Make sure that either TTM or DDI is selected If ttm.Value = False And ddi.Value = False Then MsgBox "You must select the file type before proceeding", , "File Not Selected" Exit Sub Else If ttm.Value = True Then 'opening ttm file ttmfn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _ Title:="Please select a file") If ttmfn = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else MsgBox ttmfn, , "File Name" Application.Visible = False Workbooks.Open (ttmfn) End If Workbooks(1).Activate 'Call DoesSheetExist 'Worksheets("Impedance").Activate Call WorksheetCheck(ttmfn) End If End If End Sub 'SUB: Sub WorksheetCheck(ttmfn) Workbooks(1).Activate If SheetExists("Impedance") = True Then MsgBox "Click OK to Continue", vbOKCancel, "Continue" Else MsgBox "This is not TTM layerstackup file", , "Wrong File" End If End Sub 'FUNCTION: Function SheetExists(Impedance As String) As Boolean Dim sheetcount As Integer Dim t As Integer SheetExists = False sheetcount = ActiveWorkbook.Sheets.Count For t = 1 To sheetcount If Sheets(t).Name = "Impedance" Then SheetExists = True Exit Function End If Next t End Function -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check to see if a sheet with a particular name exists?
#1. IIF is an immediate If statement. It works very similar to the =if() that
you use in excel: =if(a1=b1,"something", "something else") So if you pass a workbook to the function, then Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) wb will be that passed workbook. If you didn't pass a workbook to that function, then it'll use ThisWorkbook (the workbook with the code. You may want to replace Chip's IIF statement with an equivalent block if statement: if whichbook is nothing then set wb = thisworkbook else set wb = whichbook end if #2. The "on error resume next" line is important in this next statement. On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) If there is a worksheet by the name of the string passed to the function, then the lenth of that workbook's name will be 0. If you open a new workbook, you'll see Sheet1. Sheet1 exists and the length of its name is 6. What's the length of the sheet named Sheet99999. Since it doesn't exist, you could say anything you want--but that On error resume next will treat it as 0. It's the equivalent of: on error resume next if len(wb.worksheets(sheetname)) 0 then sheetexists = true else sheetexists = false end if ======= I like this comparison, too: dim TestWks as worksheet on error resume next set testwks = wb.worksheets(sheetname) on error goto 0 if testwks is nothing then 'the assignment failed, so it doesn't exist sheetexists = false else sheetexists = true end if #3. If you don't want to see the screen flickering around, I'd use: application.screenupdating = false 'do the work application.screenupdating = true If you're going to almost anything to that workbook, you're going to have to open it to work on it. But you can turn off the screenupdating and I bet the user doesn't even know that it's open. If you do the work, close (and save???) that workbook, then turn screenupdating back on, the user will see it as magic <vbg. Varun wrote: Dave, Thanks a lot. It works. I have a few questions. They are probably dumb since I am very new to VBA and programming in general so please bear with me if you don't mind. 1) What's this line doing in the Function SheetExists: Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) 2) Likewise, can you please explain me the meaning of following line: SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) 3) I do not want the workbook selected to be opened hence I had Application.Visible = False followed by Workbooks(1).Active. Question is, how can I get around the workbook not being launched (i.e. excel opening the selected file) yet being available for being parsed in the background? Would "activating" the ttmfnWkBk work? If yes, can please show me how i.e. what's the command to activate the workbook so that it can be parsed later? Thanks again for help. "Dave Peterson" wrote: This line: Call WorksheetCheck(ttmfn) is passing the name of the file--including the drive, path and filename. It's not checking for the existence of a worksheet. And it's always scary to me to use workbooks(1). How do you know to use the first workbook in the collection. And comparing strings could lead to an error, too. Impedance and impedance will not be the equal. And you were a little aggressive when you modified that SheetExists function. Those are variables that represent the names/objects you're passing to the function. You don't need to use the name of the sheet (Impedance) as the name of the variable. I'd try: Option Explicit Private Sub cmdbut_Click() Dim ttmfn As Variant Dim ttmfnWkbk As Workbook 'Make sure that either TTM or DDI is selected If ttm.Value = False _ And ddi.Value = False Then MsgBox prompt:="You must select the file type before proceeding", _ Title:="File Not Selected" Exit Sub Else If ttm.Value = True Then 'opening ttm file ttmfn = Application.GetOpenFilename _ (FileFilter:="Excel Files, *.xls", _ Title:="Please select a file") If ttmfn = False Then MsgBox "Stopping because you did not select a file" Exit Sub End If 'MsgBox ttmfn, , "File Name" 'I'm not sure why you wanted this. 'maybe you meant application.displayalerts = false 'or even application.screenupdating = false 'or even application.enableevents = false 'or any/all of the three. 'Application.Visible = False Set ttmfnWkbk = Workbooks.Open(ttmfn) If SheetExists(SheetName:="impedance", _ WhichBook:=ttmfnWkbk) = False Then MsgBox "Doesn't exist" Else MsgBox "rest of code here" End If End If End If End Sub Function SheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) End Function Varun wrote: Hi, VBA newbie here...I have written below code which calls out a sub and func. Problem is that when the sub procedure is called, which in turn invokes the function, I am unable to get the correct output. I want to check to see if the opened file has a worksheet named Impedance and if not, produce a message saying so...but problem is that I always get "This is not TTM Layer stackup file" message. What am I doing wrong? Thanks for help. Here's the code snippet. Private Sub cmdbut_Click() 'Make sure that either TTM or DDI is selected If ttm.Value = False And ddi.Value = False Then MsgBox "You must select the file type before proceeding", , "File Not Selected" Exit Sub Else If ttm.Value = True Then 'opening ttm file ttmfn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _ Title:="Please select a file") If ttmfn = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else MsgBox ttmfn, , "File Name" Application.Visible = False Workbooks.Open (ttmfn) End If Workbooks(1).Activate 'Call DoesSheetExist 'Worksheets("Impedance").Activate Call WorksheetCheck(ttmfn) End If End If End Sub 'SUB: Sub WorksheetCheck(ttmfn) Workbooks(1).Activate If SheetExists("Impedance") = True Then MsgBox "Click OK to Continue", vbOKCancel, "Continue" Else MsgBox "This is not TTM layerstackup file", , "Wrong File" End If End Sub 'FUNCTION: Function SheetExists(Impedance As String) As Boolean Dim sheetcount As Integer Dim t As Integer SheetExists = False sheetcount = ActiveWorkbook.Sheets.Count For t = 1 To sheetcount If Sheets(t).Name = "Impedance" Then SheetExists = True Exit Function End If Next t End Function -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check if sheet exists | Excel Discussion (Misc queries) | |||
Check if file exists | Excel Discussion (Misc queries) | |||
check if the sheet/tag exists | Excel Worksheet Functions | |||
check if worksheet exists | Excel Worksheet Functions | |||
Check if a number exists in a range? | Excel Discussion (Misc queries) |