Excel doesnt recognize workbook as open

[cbrf23]

Hi, I am using excel 2003 and I'm trying to write a macro that checks to see if a file is open, and if not then it will open it.

I've found lots of different ways of doing this. But none of them are working for me.

So i tried this:

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
Sub test()
MsgBox IsWorkbookOpen("X:\directory\sub directory\MY FILE.xls")
End Sub

And even when the workbook is open, it still returns "FALSE" in the message box.

I cant for the life of me figure out why. Its very frustrating. Can anyone offer any ideas on what the problem might be?

The only thing I can think of that might be causing issues, is that the workbook is a shared workbook. Now I dont know why that would matter, but every little bit of info counts...right?

 

[RoryA]

You should not be including the file path:

Sub test()
MsgBox IsWorkbookOpen("MY FILE.xls")
End Sub

If you want to include the file path, then you need to test wb.Fullname in the function.

 

[Andrew Poulsom]

The function expects the workbook's name only, excluding the path:

MsgBox IsWorkbookOpen("MY FILE.xls")

 

[VoG]

Try this alternative

Function IsWorkbookOpen(WorkbookName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = Len(Workbooks(WorkbookName).Name)
End Function