Is workbook open?

Posted by Ben on April 20, 2001 10:48 AM

I have tried to write a macro that If Workbook1 isnt open it will open it and then close it. But if it is open it wont open it again and it wont be closed. But I cannpt get it to work. Can anyone please tell me how to change the code to get it to do this?

Sub Workbook1()

Dim Workbook1Open As String

Workbook1Open = False

If Workbooks("Workbook 1.xls") = Active Then
Workbook1 = True
End If

If Workbook1 = False Then
Workbooks.Open ThisWorkbook.Path & "\Workbook 1.xls"
End If

MORE CODE GOES HERE!!!

If Workbook1 = True Then
Workbooks.Close "Workbook 1.xls"
End If

End Sub

Thanks

Posted by Dax on April 20, 2001 10:58 AM

How about using a For Each loop? i.e.

Sub IsWorkbookOpen()
Dim wb As Workbook, IsOpen As Boolean

For Each wb In Application.Workbooks
If wb.Name = "Workbook1" Then IsOpen = True: Exit For
Next

If IsOpen = False Then
Workbooks.Open ThisWorkbook.Path & "\Workbook 1.xls"
End If

End Sub

Regards,
Dax.

Posted by Dave Hawley on April 20, 2001 10:59 AM

Hi Ben

There are a few ways to do this, here is but one:

Sub TryThis()
Dim WIsOpen As Workbook

On Error Resume Next
Set WIsOpen = Workbooks("Book1.xls")
If WIsOpen Is Nothing Then
MsgBox "I'm not Open"
Else
MsgBox "I'm already Open"
End If
On Error GoTo 0
End Sub

Dave

OzGrid Business Applications