Checking to see if a workbook is open

[hennahairgel]

I want to check if a workbook is open, an if not open it. How do I go about doing that then?
TIA
Henry

[dk]

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 ]

[Tikas A. Planck]

Another way :-

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

[Juan Pablo González]

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.

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

[JohnG]

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.

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

[Juan Pablo González]

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

[Robb]

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?

[Juan Pablo González]

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

[Ekim]

Change the directory path and workbook name to suit.

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

Regards,

Mike