How to find if a workbook is open

A

Amir

I want to open a workbook, say "A", to perform some
calculation.However, if the workbook is already open then
I want to activate it and do the calculations. I am a
little bit confused about the code I need to decide
whether it is open or need opening. Can anyone help?
Thanks in advance.

Regards
Amir
 
B

Bob Phillips

Hi Amir,

This should do it

Function IsWBOpen(Name As String)
Dim oWB As Workbook

On Error Resume Next
Set oWB = Workbooks(Name)
On Error GoTo 0
IsWBOpen = Not (oWB Is Nothing)

End Function


Test with

If IsWBOpen("Stats Manager.xls") Then
Workbooks(Stats Manager.xls").Activate
Else
Workbooks.Open Filename:="Stats Manager.xls"
End If
....


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

tolgag

If you want to avoid use an On Error block, try following function :

Function IsWBOpen(strName As String) as Boolean

Dim w as Workbook,
Dim bResult as Boolean

bResult = False

For each w in Workbooks
if w.name = strName then
bResult = True
exit for
end if
Next

IsWBOpen = bResult

End Functio
 
A

Amir

Bob and Tolgag,

Thank you ever so much.It works and will save me a lot of
troubleshooting. This group is great. Thank you.

Regards

Amir
 
T

Tom Ogilvy

Why would one want to avoid an on Error block by replacing it with a slower
approach like looping through all the workbooks? Is there a technical
reason or just personal preference?
 
Top