testing for open workbook

[Gary Keramidas]

i've seen almost identical code in a few place, but it doesn't work, the
variable always evaluates to nothing. why? i just need to test if the other
workbook is open and if not, open it.

Dim WBook As Workbook
'...
On Error Resume Next
Set WBook = Workbooks("Workbook2")
On Error GoTo 0
If WBook Is Nothing Then
'your sorting code
End If

 

[Rowan]

Try

Set WBook = Workbooks("Workbook2.xls")

Regards
Rowan

 

[Gary Keramidas]

i've put the whole name in there and it didn't make any difference

 

[Gary Keramidas]

this seems to work for what i need

On Error GoTo OpenFile
Windows("workbook.xls").Activate
GoTo CopyRoutine

OpenFile:
'open the workbook

CopyRoutine
' do my copy routine



it if isn't open, it won't activate.

 

[Gary Keramidas]

not for somebody else, only me. i have both workbooks open in one instance
of excel. both workbooks appear under the window menu. the variable always
evaluates to nothing.

don't know why it won't work.

 

[NickHK]

Gary,
You're testing whether "Workbook2" is open in the current instance of Excel.
If it is not, the WBook will be Nothing.

What I think you mean is "Is Workbook2 already opened by somebody else ?
For that you have to try and open it:
Set WBook = Workbooks.Open (..argument..)

You may want to check out .DisplayAlerts also.

NickHK

 

[NickHK]

Gary,
It does work if there is a WB called that name.
Comment out the "On Error.. and see what error is being raised.

NickHK

 

[NickHK]

Gary,
It does work if there is a WB called that name.
Comment out the "On Error.. and see what error is being raised.

NickHK