What is the short way to find out if Excel workbook is open or not? 2

[vladk]

Thank you!

 

[Hypetia]

Try this function.
___
[tt]
Function IsWorkBookOpen(WorkBookName As String) As Boolean
On Error Resume Next
Dim XL As Object, WB As Object
Set XL = GetObject(, "Excel.Application")
If XL Is Nothing Then Exit Function 'Excel is not running
Set WB = XL.WorkBooks(WorkBookName)
If WB Is Nothing Then Exit Function 'my workbook is not opened
IsWorkBookOpen = True
End Function

Private Sub Form_Load()
MsgBox IsWorkBookOpen("MyWorkBook.xls")
Unload Me
End Sub[/tt]

 

[vladk]

Hi Hypetia,

Thank you for your solution very much! It works very well. I also tried your approach but was too tired to accomplish it. The only important thing that I added is Err.Clear.
I also rewrote it minimizing number of "If"s and removing Exit statements.

Thank you again!

vladk

Function IsWorkBookOpen(ByVal pstrWorkBookName As String) As Boolean

On Error Resume Next

Dim objXL As Object
Dim objWB As Object
Dim blnReturn As Boolean

blnReturn = False
Set objXL = GetObject(, "Excel.Application")

If Not objXL Is Nothing Then
Set objWB = objXL.Workbooks(pstrWorkBookName)
blnReturn = Not objWB Is Nothing
End If

IsWorkBookOpen = blnReturn

Err.Clear
Set objXL = Nothing
Set objWB = Nothing

End Function

 

[strongm]

>minimizing

Oh, you want the short version... ;-)

[blue]Function IsWorkBookOpen(ByVal pstrWorkBookName As String) As Boolean
    On Error Resume Next
    IsWorkBookOpen = GetObject(, "Excel.Application").Workbooks(pstrWorkBookName).Name <> ""
 End Function[/blue]

 

[vladk]

Wow...

We still need Err.Clear

Thank you!

 

[Hypetia]

>We still need Err.Clear

Not necessary. We should use "[tt]On Error Goto 0[/tt]" instead which is more appropriate to turn off an error handler.

 

[strongm]

Exiting the function should turn off the error handler and clear the event...it just doesn't clear the Err object

 

[Shake412]

Firstly, thanks guys this came in very useful. But what about if there is more than one instance of Excel running?

I’m writing an app which automates some excel report writing. I’ve found that if the user already has excel open and another is created in the background the GetObject function finds the first instance. How do I find the second instance?

I’ve tried looping around all the workbooks, with obviously doesn’t work as they are separate individual instances.

Any thoughts?

Thanks
Paul