How to CheckIn a checked out file in VBA?
 

Hi all,

In an Excel VBA subroutine, I need to check out a file (of
SharePoint), change a few records, then save the changes and check the
file back in. So far I have been able to check out the file, open it,
modify it, but then I get stuck.

The examples I started with came from he

http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

The test subroutine I am using is below. I am able to check out the
file. After that I can open the file and make changes. What I cannot
do so far is check in the file. You can see from the various
permutations I have tried, in the comments, what my results are. If I
just stop the code when it breaks, I can manually check in the file.

The file path is a named range on the worksheet, as is the name of the
worksheet to modify.

Can anyone show me what I am doing wrong?

Thanks!

Sub testcheckinout()
Dim dbpath As String
Dim wbk2 As Workbook
Dim sdbsheet As String

dbpath = Range("DBpath").Value
sdbsheet = Range("DBsheet").Value

If Workbooks.CanCheckOut(dbpath) = True Then
Workbooks.CheckOut dbpath
Else
MsgBox "Unable to check out this document at this time."
Exit Sub
End If

' should be checked out. Now open it
Set wbk2 = Workbooks.Open(dbpath, , False)
Set dbsheet = wbk2.Sheets(sdbsheet)

' Modify a cell
dbsheet.Cells(2, 1) = "This is a change " & Date & " at " & Time

' Save changes. But if I do this I cannot check in the file
'wbk2.Close SaveChanges:=True
' check in

'If Workbooks(dbpath).CanCheckIn = True Then ' fails - subscript
out of range
If wbk2.CanCheckIn = True Then ' fails, automation error if file
is closed first
'Workbooks(dbpath).CheckIn ' fails, subscript out of range
'wbk2.CheckIn ' fails, automation error
'wbk2.CheckIn ' fails, method checkin of object
'Workbooks(2).CheckIn ' fails, method checkin of object
'Workbooks.CheckIn dbpath ' this doesn't compile
Workbooks(dbpath).CheckIn ' fails, subscript out of range
MsgBox dbpath & " has been checked in."
End If

End Sub