Check whether a Excelworkbook is open

[Crookshanks]

Hello there,
I've got a simple question. What is the most easiest way in VBA to check whether a certain workbook is open without opening the workbook? Any help appreciated.
Regards,

 

[Rofeu]

well, easiest way is probably to try to activate it and trap the error if it's not open.

Something along the lines of

Function WorkbookOpen(WB_Name As String) As Boolean

On Error Resume Next
Application.Windows(WB_Name).Activate

WorkbookOpen = Err.Number = 0

Err.Clear
On Error GoTo 0

End Function

Cheers,

Roel

 

[Crookshanks]

Thanks for your quick answer, but I am afraid that I made a mistake in the question. I want to check whether another user had the workbook open...

 

[Rofeu]

ah, that's a completely different ballgame...and unfortunately I do not have a ready answer to that.

 

[JerryKlmns]

If you try to use the FileCopy statement on a currently open file, an error occurs. So trap that and move on!

 

[Crookshanks]

OK, thanks this works!