Open & Check out Excel workbook from SharePoint

I've found through trial and error that Workbooks.CanCheckOut (Filename:= FullName) where FullName is the URL for the SharePoint file only works for files that are not open in the current instance of Excel.

The method will always return False if you have the file open in the current instance of Excel which is obviously the case here. Workbooks.CheckOut (ActiveWorkbook.FullName) opens the file, checks it out and then inexplicably, closes the file. So opening and checking out a SharePoint file becomes a 3 step process.

Sub CheckOutAndOpen()
Dim TestFile As String

TestFile = "http://spserver/document/Test.xlsb"
If Workbooks.CanCheckOut(TestFile) = True Then
    Workbooks.CheckOut(TestFile)
    Workbooks.Open (TestFile)
Else
    MsgBox TestFile & " can't be checked out at this time.", vbInformation
End If
End Sub

This is all a bit counter intuitive because when working manually with SharePoint files you have to open them to see if they can be checked out and then perform the check-out operation. Neither MSDN or Excel VBA help mention that the Workbooks.CanCheckOut (Filename:= FullName) method always returns False if you have the file open in the current instance of Excel.

The other methods never worked for me. This will CheckOut the file and either open it hidden and terminate (Visible = False), or you can just have it open (Visible = True) and remove the Quit, BUT while the doc is Checked out, I can't seem to target or check in that mXLApp doc further. The solution is to not leave the mXLApp doc open, but then once closed to open that same doc as normal, and then it will Check in with the Check in code line.

Sub TestCheckOut()
    Dim FileName as String 
    FileName  = "http://spserver/document/Test.xlsx"

    SP_CheckOut FileName
End Sub

Sub SP_CheckOut(docCheckOut As String)
    Set mXlApp = CreateObject("Excel.Application")
    ' Determine if workbook can be checked out.
    ' CanCheckOut does not actually mean the doc is not currently checked out, but that the doc can be checked in/out.
    If mXlApp.Workbooks.CanCheckOut(docCheckOut) = True Then
        mXlApp.Workbooks.Open fileName:=docCheckOut
        mXlApp.Workbooks.CheckOut docCheckOut
        ' False is hidden
        mXlApp.Visible = False
        mXlApp.Quit
        Set mXlApp = Nothing
        
        Workbooks.Open fileName:=docCheckOut

    Else
        MsgBox "Unable to check out this document at this time."
    End If
End Sub  

As for Checkin, can't get any methods to work except:

Workbooks(CheckName).checkin SaveChanges:=True, Comments:=""

Sub CheckIn(CheckName As String, CheckPath As String)
' Must be open to save and then checkin
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(CheckName)
    If Err = 0 Then
        WorkbookIsOpen = True
    Else
        WorkbookIsOpen = False
        
        Set wb = Workbooks.Open(CheckPath)
    End If

    wb.CheckIn SaveChanges:=True, Comments:=""

End Sub

I did try using a Query on the SharePoint browser link to determine who has the doc checked out (if anyone). This worked sometimes. If it did work, half the time it would take too long to be useful, and the other half of the time it would throw a timeout error. Not to mention the query would disrupt other processes, like saving or certain other macros. So I put together a WebScrape which quickly returns who might have the doc checked out.

Sub TestWho()
    Dim SPFilePath As String  

    SPFilePath = "http://teams.MyCompany.com/sites/PATH/PATH/Fulfillment/Forms/AllItems.aspx"

    Debug.Print CheckedByWho(SPFilePath , "YOURdocName.xlsx")
End Sub

Function CheckedByWho(ShareFilePath As String, ShareFileName As String)
    Dim ie As Object
    Dim CheckedWho As String
    Dim ImgTag As String
    Dim CheckStart, CheckEnd As Integer
    Dim SplitArray() As String
    
    Set ie = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")

    With ie
        .Visible = False
        .Navigate ShareFilePath
        Do Until .readyState = 4: DoEvents: Loop
        Do While .Busy: DoEvents: Loop
    End With

    CheckedWho = "Not Check Out"

    For Each objLink In ie.document.getElementsByTagName("img")
        ImgTag = objLink.outerHTML

        CheckedOutPos = InStr(objLink.outerHTML, ShareFileName & "
Checked Out To:")
        If CheckedOutPos > 0 Then
            CheckStart = InStr(objLink.outerHTML, "Checked Out To: ")
            CheckedWho = Mid(objLink.outerHTML, CheckedOutPos + 41)

            SplitArray = Split(CheckedWho, """")
            CheckedWho = SplitArray(0)
       End If

    Next objLink

    CheckedByWho = CheckedWho

    ie.Quit
End Function