Test If Workbook is Open in Network and On Users Computer
 

I need a macro that currently tests if a workbook is open. The Archive
workbook is saved on our network server. The code below only works if the
workbook is open on the users computer. How can I test if anyone has the
workbook open in the network and the users computer?

Sub OpenFile()

' open archive workbook if not open or tell user to close it first
If IsWorkbookOpen(strArchiveName) Then
strPrompt = "The Archive workbook is already open. "
strPrompt = strPrompt & "Finish what you are doing, close it and try
again."
intButtons = vbCritical
strTitle = "Problem"
MsgBox strPrompt, intButtons, strTitle
Exit Sub
Else
With Application
.StatusBar = "Sending Items to the Archive...Please Wait"
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strArchiveName,
WriteResPassword:="AdTech"
End If

End Sub


Public Function IsWorkbookOpen(ByVal wbkName As String) As Boolean

SubName = "IsWorkbookOpen"

On Error Resume Next
IsWorkbookOpen = Not (Application.Workbooks(wbkName) Is Nothing)

End Function
--
Cheers,
Ryan