How to find if a workbook is open

  • Thread starter Thread starter Amir
  • Start date Start date
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
 
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)
 
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
 
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
 
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?
 
Back
Top