R
Revolvr
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 here:
http://msdn.microsoft.com/en-us/library/aa223821(office.11).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
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 here:
http://msdn.microsoft.com/en-us/library/aa223821(office.11).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