Workbook.CheckIn always produces an error the first time it's called? (x-posted on StackOverflow)

Question

This question is cross-posted on SO here.

I have some workbooks stored in a document library on Sharepoint 2007. I want to check out a workbook, modify it, and check it back in.

Using the following code:

Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test()

Dim bk As Workbook
Dim path As String

path = "http://sharepoint/sites/test/TEST_Relink.xlsm"

If Workbooks.CanCheckOut(path) Then
    Application.DisplayAlerts = False
    Workbooks.CheckOut path
    DoEvents
    Set bk = Workbooks.Open(path, False)
    bk.Sheets("test").Range("h1").Value = "modified " & Now
    DoEvents
    Sleep 10000
    bk.checkIn True
    Application.DisplayAlerts = True
End If


End Sub

The bk.checkIn call always produces the following run-time error:

Method 'CheckIn' of object '_Workbook' failed

After I go into Debug, I press F5 to continue and the check-in always occurs successfully.

I added the 10-second delay with Sleep 10000 because I was thinking that maybe the check-out was taking a while to propagate to the server. But no matter how much time I set for Sleep, this same issue keeps occurring.

I tried using a looped check of .CanCheckIn as follows:

While Not bk.CanCheckIn
DoEvents
Wend
bk.checkIn True

This gave the same error.

I also tried this solution and got the same error.

Answer 1

This post is 7 years old, and I still see a lot of people struggling with this error ("Method 'CheckIn' of object '_Workbook' failed"). I finally found a solution that worked for me.

Instead of executing the check-in in a single line of code (as shown below), I broke it down into two lines of code, first saving the file and then checking it in.

Assume "docCheckIn" is a string variable that contains the name of the file you want to check in and "sComment" is also a string variable that contains whatever comment you want to record.

Instead of writing the code like this:

    Workbooks(docCheckIn).CheckIn SaveChanges:=True, Comments:=sComment

Do this:

    Workbooks(docCheckIn).Save
    Workbooks(docCheckIn).CheckIn Comments:=sComment