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)

"Amir" wrote in message
...
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

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

--
Message posted from http://www.ExcelForum.com

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

-----Original Message-----
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)

"Amir" wrote in
message
...
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


.

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?

--
Regards,
Tom Ogilvy

tolgag wrote in message
...
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 Function


---
Message posted from http://www.ExcelForum.com/