Test If Workbook is Open in Network and On Users Computer

R

RyanH

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exporting PivotCharts and PivotTables from a workbook without"breaking" them 0
Save Copies of Workbook with Before Save Event 1
Fool Proof Price Validation Custom Function 0
Validate Text in Textbox to Ensure Text is a Valid Price Format 1
Test if a Workbook is Open 3
Checking if a workbook is open 7
Move some sheets to a new workbook, without macros, and withoutbreaking link btwn Pivot Tables and P 0
Test to see if a workbook is open 2

Top