Use Access VBA to check-out an Excel doc from Sharepoint

Question

I have a rather odd situation, the below code can successfully checkout a file from sharepoint to Excel...

Private sub checkoutfromSP()
Dim loc as String
loc = "Location"

if Workbooks.CanCheckOut(loc) = true then
Workbooks.CheckOut loc
end if

However how does this translate into Access? I always receive the error "This document cannot be checked out" with the following code?

Dim objXL as Excel.Application
Dim loc as String

loc = "Location"
objXL = new Excel.Application

if objXL.Workbooks.CanCheckOut(loc) = True then
objXL.Workbooks.CheckOut loc
end if

Reason for the checkout via Access is there are a few pieces of data that need dropped into Excel from Access, however as the Excel file is on sharepoint I need to checkout/checkin to submit the changes.

Blackhawk · Accepted Answer · 2014-04-10 21:41:17Z

1

+50

Open the document with your Excel instance before checking it out and it should work for you:

Dim objXL As Excel.Application
Dim objWB As Excel.Workbook 'NEW
Dim loc As String

loc = "Location"

Set objXL = New Excel.Application  'Make sure you use Set here

If objXL.Workbooks.CanCheckOut(loc) = True Then
    Set objWB = objXL.Workbooks.Open(loc)  'NEW
    objXL.Workbooks.CheckOut loc
End If

When you check the workbook back in with the line objWB.CheckIn, Excel automatically closes the Workbook object.

answered Apr 10, 2014 at 21:41

Blackhawk

6,1804 gold badges31 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use Access VBA to check-out an Excel doc from Sharepoint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related