Checking to see if a workbook is open

D

dk

MrExcel MVP

Joined

Feb 15, 2002

Messages

2,942

• Feb 20, 2002

• #2

There are several ways to do this - here is a function that you can use either in code or in a worksheet:-

Function IsWorkbookOpen(WorkbookName As String) As Boolean
Dim wb As Workbook

For Each wb In Excel.Workbooks
If UCase$(wb.Name) = UCase$(WorkbookName) Then
IsWorkbookOpen = True
Exit Function
End If
Next
End Function


You could use it like this in code:-

Sub test()
MsgBox IsWorkbookOpen("Yourworkbook.xls")
End Sub

or like this in a worksheet:-

=IsWorkbookOpen("Yourworkbook.xls")

Both will return TRUE if the workbook is open.

HTH,
D
This message was edited by dk on 2002-02-20 04:38

 

Upvote 0

L

Legacy 139

Guest

• Feb 20, 2002

• #3

Another way :-

On Error Resume Next
Workbooks("TheWB.Xls").Activate
If Err <> 0 Then Workbooks.Open("C:TheWB.xls")
On Error GoTo 0

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Feb 20, 2002

• #4

I use this function. It's similar to the last one proposed, but I don't like Activating the workbook, because you would have to reactivate your "working" workbook. Try this.

Code:

Function WBisOpen(Bk As String) As Boolean
Dim T As Workbook
Err.Clear
On Error Resume Next
Set T = Workbooks(Bk)
WBisOpen = Not (Err.Number > 0)
Err.Clear
On Error GoTo 0
End Function

 

Upvote 0

J

JohnG

Board Regular

Joined

Feb 18, 2002

Messages

165

• Feb 20, 2002

• #5

I just went for the simplistic approach...

Sub TestCall()
Dim TCount As Integer
Dim TLoop As Integer

TCount = Workbooks.Count
For TLoop = 1 To TCount
If Workbooks(TLoop).Name = "Calltest.xls" Then
'code
End If
Next TLoop
End Sub

On 2002-02-20 05:53, Juan Pablo G. wrote:
I use this function. It's similar to the last one proposed, but I don't like Activating the workbook, because you would have to reactivate your "working" workbook. Try this.

Code:

Function WBisOpen(Bk As String) As Boolean
Dim T As Workbook
Err.Clear
On Error Resume Next
Set T = Workbooks(Bk)
WBisOpen = Not (Err.Number > 0)
Err.Clear
On Error GoTo 0
End Function

Click to expand...

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Feb 20, 2002

• #6

Ok, simple, agree, but long and not very efficient...

 

Upvote 0

R

Robb

Board Regular

Joined

Feb 17, 2002

Messages

145

• Nov 23, 2002

• #7

This function only works when you have 1 instance of Excel open.
What if you have 2 or more instances of Excel? How can you check to see if the file is open?

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Nov 23, 2002

• #8

You can use GetObject to try to search for the workbook object.

 

Upvote 0

E

Ekim

Well-known Member

Joined

Jun 30, 2002

Messages

1,416

• Nov 24, 2002

• #9

Change the directory path and workbook name to suit.

<pre>
Sub OpenIt()
Dim MyDir As String
MyDir = "C:My DocumentsTempParts1.xls"

If Dir(MyDir) <> "" Then
Workbooks.Open ("C:My DocumentsTempParts1.xls")

End If

End Sub

</pre>

Regards,

Mike

 

Upvote 0