VBA code to determine whether a SharePoint file is checked out?

Question

I've written a VBA program to examine each Excel workbook in a SharePoint folder and perhaps move it to another location. It turns out that rarely (but not never) a user is currently editing a workbook even though it's already completed and ready to be moved; presumably the owner is making last-minute changes. In that case my program, all unawares, moves the workbook to another folder and the user subsequently saves it. The result is two copies of the workbook, whereupon Bad Things happen.

So I'd like the program, as part of its examination of each workbook, to be able to determine whether the file is currently checked out. I've heard there's some sort of object model for SharePoint, and from what I read here it must be true. So I'd like one or maybe two answers:

Second preference: How can I determine in a VBA program whether a SharePoint file is currently checked out? I've seen very similar questions answered here, but they assumed a knowledge of the SP object model that I don't possess; I need to start from the creation of a SharePoint object.

First preference: Where can I find documentation on a SharePoint class I can use in VBA?! I keep seeing references to it but can't seem to find the starting point.

Answer 1

If you are running your VBA code within the an Excel application, then you can use the CanCheckOut property of the Workbooks collection in the Excel object model.

Workbooks.CanCheckOut(Path) will return true if a workbook is checked out, in which case you could avoid copying it.

That may give you a quick solution if you are in Excel and if you have the full path of the files, but might not be the ultimate preferred solution depending on what else you have going on.

I don't know how to do this directly through a Sharepoint object model or API but would also be interested in seeing such a solution.

Answer 2

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