MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Checking to see if a workbook is open


Posted by matk on January 20, 2002 3:42 PM

I would like to check to see if a workbook is open already before attempting to open it. I cannot think of a way to make this happen, and I cant find anything in the vbhelp folder either.

Any help would be much appreciated.

Cheers


Posted by DK on January 20, 2002 4:00 PM

Hello,

There are a number of ways of doing this. One way is like this:-

Sub IsItOpen()
Dim strFileName As String, wbEnum As Workbook, blnIsOpen As Boolean

strFileName = "C:\temp\Mybook.xls"

For Each wbEnum In Excel.Workbooks
If wbEnum.Path & "\" & wbEnum.Name = strFileName Then blnIsOpen = True: Exit For
Next

If blnIsOpen = False Then 'Workbook isn't open
Workbooks.Open strFileName
End If

End Sub

HTH,
D

Posted by DK on January 20, 2002 4:07 PM

Re: Oops, one small change needed

Because the comparison will be case sensitive (unless you have Option Compare Text at the top of the module) you should change the code to this:-

Sub IsItOpen()
Dim strFileName As String, wbEnum As Workbook, blnIsOpen As Boolean
strFileName = "C:\Temp\Mybook.xls"

For Each wbEnum In Excel.Workbooks
If UCase(wbEnum.Path & "\" & wbEnum.Name) = UCase(strFileName) Then blnIsOpen = True: Exit For
Next

If blnIsOpen = False Then 'Workbook isn't open
Workbooks.Open strFileName
End If

End Sub

It just makes the file you're looking for and the name of each workbook UPPER case.

Regards,
D

Posted by matk on January 20, 2002 6:50 PM

Re: Oops, one small change needed

Thanks, youre a champion!

Posted by Juan Pablo G. on January 21, 2002 9:06 AM

Re: Oops, one small change needed

How about this UDF ?

Public Function IsBookOpen(BName As String) As Boolean
Dim WBk As Workbook
On Error Resume Next
If InStr(1, BName, ".xls", 1) = 0 Then BName = BName & ".xls"
Set WBk = Workbooks(BName)
IsBookOpen = (Err = 0) + 0
Set WBk = Nothing
End Function

Juan Pablo G.