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.
