Test to see if a workbook is open

[GTyson2]

Ok I'm getting a subscript out of range when I'm trying to see if a workbook
is open. The file name for the workbook relates to the sheet that is
currently in use. The following is my code (Simplified of course) can someone
please help me get this working.

Thanks in advance.

Dim ThisWB As String
Dim PropName As String
Dim AcctType As String
Dim Today As String
ThisWB = ThisWorkbook.Name
PropName = ActiveSheet.Range("Prop_Name").Value
AcctType = ActiveSheet.Range("Account_Type").Value
Today = Format(Date, "MMddyyyy")
Dim NewWBN As String
NewWBN = "IMCashbook_" & PropName & "_" & AcctType & "_" & Today & ".xls"
Application.ScreenUpdating = False


If Workbook(NewWBN) Is Nothing Then
MsgBox ("New workbook is open")
ElseIf Not (Workbook(NewWBN) Is Nothing) Then
MsgBox ("New workbook is not open")

 

[Gary''s Student]

Here is an easy way to see if a workbook is open:

Sub IsItOpen()
Text = "sample.xls"
For Each wb In Workbooks
If wb.Name = Text Then Exit Sub
Next
MsgBox Text & " is not open"
End Sub

 

[Mike H]

Hi,

Try it this way

Dim ThisWB As String
Dim NewWBN As Workbook
Dim PropName As String
Dim AcctType As String
Dim Today As String
ThisWB = ThisWorkbook.Name
PropName = ActiveSheet.Range("Prop_Name").Value
AcctType = ActiveSheet.Range("Account_Type").Value
Today = Format(Date, "MMddyyyy")
On Error Resume Next
Set NewWBN = Workbooks("IMCashbook_" & PropName & "_" & AcctType & "_" &
Today & ".xls")
Application.ScreenUpdating = False


If NewWBN Is Nothing Then
MsgBox ("New workbook is not open")
Else
MsgBox ("New workbook is open")
End If
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.