Check out Excel file from SharePoint; if no other Excel file is op
 

Im opening (checking out) an Excel file from SharePoint and trying to apply
a test to see if there is another Excel file in SharePoint already open. If
there is an Excel file open, I want a prompt to be displayed to a user. If
there is currently no Excel file checked out, then the user can open the file
without any problem. Heres the code I have so far:

Option Explicit

Sub UseCanCheckOut()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim xlFile As String

xlFile = "https://globalconsumer.w.net/sites/Shared%20Documents/test/" &
ActiveWorkbook.Name

If Workbooks.CanCheckOut(ActiveWorkbook.Path & "/" & ActiveWorkbook.Name) =
True Then
Workbooks.CheckOut (ActiveWorkbook.Path & "/" & ActiveWorkbook.Name)
MsgBox "Is checked out to you."
Else
MsgBox "Cannot be checked out to you."
End If

'Determine if workbook can be checked out.
If Workbooks.CanCheckOut(xlFile) = True Then
Workbooks.CheckOut xlFile
Set xlApp = New Excel.Application
xlApp.Visible = True
Set wb = xlApp.Workbooks.Open(xlFile, , False)
' MsgBox wb.Name & " is checked out to you."
' Else
' MsgBox "You are unable to check out this document at this time."
End If

Call MyMacro

End Sub

Sub MyMacro()
If
IsFileOpen("https://globalconsumer.w.net/sites/Shared%20Documents/test/" &
ActiveWorkbook.Name) Then
' Display a message stating the file in use.
MsgBox "Another similar file is already in use!"

End If

End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next
filenum = FreeFile()

Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0

Select Case errnum
Case 0
IsFileOpen = False

Case 70
IsFileOpen = True

End Select

End Function

I think it's going to be something like that. I'm certainly not an expert
with SharePoint!!