How to check to see if a sheet with a particular name exists?

[Varun]

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]

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]

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]

#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>.