VB Is a workbook open

[linda]

In running a Visual Basic macro in Excel 2002, I have the
following situation.

The macro opens another workbook (that already exists) to
read and write some values in it.
If the other workbook is already open, then it has to be
closed and reopened each time (in response to a dialog
box) or the macro terminates.
I would like to omit the request to open the other
workbook in the event that it is already open. But I
cannot find any function in Visual Basic that can return
the status of the other workbook (i.e., whether or not it
is already open).

 

[Norman Jones]

Hi Linda,

Dim WB as Workbook

On Error Resume Next
Set WB = Workbooks("YourWorkbookName")
On Error Goto 0

If Not WB is Nothing Then
'Workbook is open
Else
'Workbook not open
End if

 

[Tom Ogilvy]

Dim bk as Workbook
Dim bOpen
On Error Resume Next
set bk = workbooks("Mybook.xls")
On Error goto 0
bOpen = False
if not bk is nothing then
' wkbk is open do nothing
bOpen = True
Else
set bk = Workbooks.Open("C:\Myfiles\Mybook.xls")
End if
' at this point bk holds a reference to Mybook.xls

.. . .


if not bOpen then
bk.Close Savechanges:=False
End if

End Sub

 

[Bernie Deitrick]

Linda,

On Error Resume Next
Windows("Filename.xls").Close False 'To not save changes, True to save
Workbooks.Open "C:\Excel\Filename.xls"

HTH,
Bernie
MS Excel MVP