Hey All,

I need a simple way to check if my ReportBook (Workbook Object) is
Open.
Right now users hit a button to populate some data, but If the user
selects new data for population and hits the button, I want to check
that the RecordBook is not currently "in-use" or "assigned" to another
Excel Workbook (namely the previous one).

As well, I would like for when the user manually closes Excel, that the
ReportBook is also 'closed' 'realeased' or whatever the proper protocol
should be for not leaving a whole bunch of objects drifting around
without closure ;) This way, when the user hits the above mentioned
button, the program recognizes the ReportBook Object as not assigned to
anything (or available for use).

Thanks!

Amanda

I found this macro, but never used it. Have a look to see if it is useful
this macro opens the workbook if it is not opened already, but you can modify.

Public Sub IsWorkbookOpen()
Dim Wb As Workbook, wkbk As Variant
'get the dialog box to open the file
wkbk = Application.GetOpenFilename("Excel Files,*.xls")
If wkbk = False Then Exit Sub
'check if its already opened in the Windows collection
Dim flag As Boolean
flag = False
For Each Wb In Workbooks
If Wb.Path & "\" & Wb.Name = wkbk Then
MsgBox "File is already open", vbExclamation, "Workbook Open"
Wb.Activate
flag = True
End If
Next Wb
If flag = False Then Workbooks.Open (wkbk)
End Sub


--
caroline


"Amanda V" wrote:

Hey All,

I need a simple way to check if my ReportBook (Workbook Object) is
Open.
Right now users hit a button to populate some data, but If the user
selects new data for population and hits the button, I want to check
that the RecordBook is not currently "in-use" or "assigned" to another
Excel Workbook (namely the previous one).

As well, I would like for when the user manually closes Excel, that the
ReportBook is also 'closed' 'realeased' or whatever the proper protocol
should be for not leaving a whole bunch of objects drifting around
without closure ;) This way, when the user hits the above mentioned
button, the program recognizes the ReportBook Object as not assigned to
anything (or available for use).

Thanks!

Amanda

Try the following

Public Function IsWorkbookOpen(ByVal sWBName$) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(sWBName)
IsWorkbookOpen = (Err = 0)
On Error GoTo 0
End Function

Note that this will only report on those workbooks that are open in the same
instance of the excel application. I am using Excel 2003 and in this version
if you have Book1.xls and Book2.xls open in one instance of Excel and
Book3.xls open in the other instance of Excel then the function written say
in the Module in Book1.xls will return true when called like this
Debug.print IsWorkbookOpen("Book1.xls")
Debug.print IsWorkbookOpen("Book2.xls")

but will return False when called for Book3.xls like this
Debug.Print IsWorkbookOpen("Book3.xls")


"Amanda V" wrote:

Hey All,

I need a simple way to check if my ReportBook (Workbook Object) is
Open.
Right now users hit a button to populate some data, but If the user
selects new data for population and hits the button, I want to check
that the RecordBook is not currently "in-use" or "assigned" to another
Excel Workbook (namely the previous one).

As well, I would like for when the user manually closes Excel, that the
ReportBook is also 'closed' 'realeased' or whatever the proper protocol
should be for not leaving a whole bunch of objects drifting around
without closure ;) This way, when the user hits the above mentioned
button, the program recognizes the ReportBook Object as not assigned to
anything (or available for use).

Thanks!

Amanda