Check if Excel file is already open

[Robert Crandal]

I need to check if an Excel file is already open.
How do i do this?

thank u

 

[Per Jessen]

Hi

Look at this:

Sub Test()
If Not IsWbOpen("Book1.xls") then
Workbooks.Open("Book1.xls")
End If
End Sub

Function IsWbOpen(WbName As String) As Boolean
For Each wb In Application.Workbooks
If wb.Name = WbName Then
IsWbOpen = True
Exit For
End If
Next
End Function

Regards,
Per

 

[Robert Crandal]

Hi Per Jessen

Does your function need to have the path along with
the filename itself?? I'm just curious if it would better
to use "C:\Mydata\Book1.xls" as opposed to just
"Book1.xls" as the parameter to your function.

thank u

 

[Mike H]

Try this

Sub nn()
MsgBox IsBKOpen("MyBook.xls")
End Sub


Function IsBKOpen(ByRef BName As String) As Boolean
On Error Resume Next
IsBKOpen = Not (Application.Workbooks(BName) Is Nothing)
End Function

Mike

 

[Per Jessen]

Hi Robert

No path needed in my function.

Regards,
Per

 

[Dave Peterson]

Just to add to Per's response.

His function doesn't need and can't use the path.

This kind of thing would fail to do what you want:
workbooks("C:\somepath\somename.xls")

The workbooks collection only cares about the name of the workbook.

You could enhance Per's function to look to see if there is any book1.xls open.
And if there is, look to see if it's the one from the folder you want.

Option Explicit
Sub testme()
MsgBox IsWorkBookOpen(myPath:="C:\my documents\excel", _
WkbkName:="Book1.xls")
End Sub
Function IsWorkBookOpen(myPath As String, WkbkName As String) As String

Dim TestWkbk As Workbook
Dim myMsg As String

Set TestWkbk = Nothing
On Error Resume Next
Set TestWkbk = Workbooks(WkbkName)
On Error GoTo 0

If TestWkbk Is Nothing Then
myMsg = "Not Open"
Else
'remove the trailing slash if there
If Right(myPath, 1) = "\" Then
myPath = Left(myPath, Len(myPath) - 1)
End If
If LCase(myPath) = LCase(TestWkbk.Path) Then
myMsg = "It's Open!"
Else
myMsg = "A file with the same name is open!"
End If
End If

IsWorkBookOpen = myMsg

End Function

You could just pass it the fullname (path and name) and do some parsing in the
function if you wanted.