Checking to see if a workbook is open

hennahairgel

Board Regular
Joined
Feb 19, 2002
Messages
63
I want to check if a workbook is open, an if not open it. How do I go about doing that then?
TIA
Henry
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
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
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
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
 
Upvote 0
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
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

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top