Hello everyone,

I have a workbook (UserFile) that when opened, updates based on a file stored on a Sharepoint site (DirectoryFile). This refresh macro can be called in the workbook by the user as well. There is also a survey in the UserFile that when submitted, writes to a table in the DirectoryFile, saves and closes it. The DirectoryFile, depending on the network is only accessed for a few seconds. If the DirectoryFile is already opened by another user, whether they are an admin updating the Directory File, or someone else is submitting a survey, I do not want the user (UserFile) to be given the option to "read only" or to see who is actively using the workbook. I searched around and found this function to accomplish the task when the file is in use, but I can only get it working on a file stored locally ("C:\Desktop\DirectoryFile.xlsx") and not the https:// path I am referencing.

The (DirectoryFile) can be read only on update/refresh but I want to make it completely unavailable if (Userfile) is submitting a survey which is a separate macro.

Any assistance is greatly appreciated.

-Curtis

I was getting an error 52 when plugging in the path "https://. . . /DirectoryFile.xlsx"


Function IsWorkBookOpen(FileName As String) as Boolean
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function


If IsWorkBookOpen("C:\Desktop\DirectoryFile.xlsx") = False Then
         Workbooks.Open Filename:=  "C:\Desktop\DirectoryFile.xlsx"
   ' continue running code . . . 
Else
   'Sorry but this WB is unavailable . . .
   'Option to try again . . .
End If