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



I’m 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. Here’s the code I have so far:

Option Explicit

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

xlFile = " Documents/test/" &

If Workbooks.CanCheckOut(ActiveWorkbook.Path & "/" & ActiveWorkbook.Name) =
True Then
Workbooks.CheckOut (ActiveWorkbook.Path & "/" & ActiveWorkbook.Name)
MsgBox "Is checked out to you."
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()
IsFileOpen(" Documents/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!!

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