Checking for an active workbook

[mcgrathm]

I want to be able to check whether a workbook is already in memory so that my macro can either switch to it or load it up from the network.

[ This Message was edited by: mcgrathm on 2002-03-19 08:24 ]

[Al Chara]

Try the following code:

BookName = "Insert Workbook name to check"
BookCount = Workbooks.Count
For i = 1 To BookCount
If BookName = Workbooks(i).Name Then
BookCheck = 1
GoTo 1
Else: BookCheck = 2
End If
Next i
1 If BookCheck = 1 Then MsgBox "Workbook is loaded"
If BookCheck = 2 Then Application.GetOpenFilename

Insert the Workbook name to check and then the code checks to see if the workbook is open, if it is not open then it opens the file open dialog box.

[Juan Pablo González]

Another suggestion:

Function BookOpen(wbName as String) as Boolean
Dim wb as Workbook
On Error Resume Next
Set wb = Workbooks(wbName)
BookOpen = Not (Err.Number > 0)
Exit Function

You can use it like this:

BookOpen("MyBook.xls")

it should run much faster.

[mcgrathm]

Thanks very much for the code, very useful