VBA code to checkout and checkin sharepoint document

Question

0.00/5 (No votes)

See more:

Hello all,

I have an requirement where an Excel 2007 macro needs to check out an excel document uploaded on SharePoint site and needs to check In. I tried with the code block below but the code is not able to check out the file. Please give your feedbacks

VB

Sub Macro1()
'
' Macro1 Macro
'
 
Dim xlApp As Excel.Application
Dim wbkReleaseFile As Workbook

    Dim strFilePath As String
    strFilePath = "" 'SharePOint URL 

    If Workbooks.CanCheckOut(strFilePath) = True Then
           Workbooks.CheckOut strFilePath
    Else
        MsgBox "Unable to check out this document at this time."
    End If
     
    Set wbkReleaseFile = Workbooks.Open(strFilePath, , False)
    
    wbkReleaseFile.Activate
    Sheets(1).Select
    Cells(4, 4).Value = "Modified"
    
    ' Determine if workbook can be checked in.
    If Workbooks(strFilePath).CanCheckIn = True Then
        Workbooks(strFilePath).CheckIn
        MsgBox strWkbCheckIn &amp; " has been checked in."
    Else
        MsgBox "This file cannot be checked in " &amp; _
            "at this time.  Please try again later."
    End If

'
End Sub

Moved from OP's answer:

The error message that I get is "Not able to check out the file". The file path is correct. Even I tried to check out .xls and .xlsx both are not working.

Posted 24-Jan-11 15:48pm

Member 4467247

Updated 23-Jun-21 7:02am

JF2015

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 15259759 · Answer 1 · 2021-06-23T07:02:00

The other methods never worked for me. This will CheckOut the file and either open it hidden and terminate (Visible = False), or you can just have it open (Visible = True) and remove the Quit, BUT while the doc is Checked out, I can't seem to target or check in that mXLApp doc further. The solution is to not leave the mXLApp doc open, but then once closed to open that same doc as normal, and then it will Check in with the Check in code line.

Sub TestCheckOut()
    Dim FileName as String 
    FileName  = "http://spserver/document/Test.xlsx"

    SP_CheckOut FileName
End Sub

Sub SP_CheckOut(docCheckOut As String)
    Set mXlApp = CreateObject("Excel.Application")
    ' Determine if workbook can be checked out.
    ' CanCheckOut does not actually mean the doc is not currently checked out, but that the doc can be checked in/out.
    If mXlApp.Workbooks.CanCheckOut(docCheckOut) = True Then
        mXlApp.Workbooks.Open fileName:=docCheckOut
        mXlApp.Workbooks.CheckOut docCheckOut
        ' False is hidden
        mXlApp.Visible = False
        mXlApp.Quit
        Set mXlApp = Nothing
        
        Workbooks.Open fileName:=docCheckOut

    Else
        MsgBox "Unable to check out this document at this time."
    End If
End Sub

As for Checkin, can't get any methods to work except:

Workbooks(CheckName).checkin SaveChanges:=True, Comments:=""

Sub CheckIn(CheckName As String, CheckPath As String)
' Must be open to save and then checkin
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(CheckName)
    If Err = 0 Then
        WorkbookIsOpen = True
    Else
        WorkbookIsOpen = False
        
        Set wb = Workbooks.Open(CheckPath)
    End If

    wb.CheckIn SaveChanges:=True, Comments:=""

End Sub

leckey · Answer 2 · 2011-01-27T10:31:00

Solution 1

You need to be more specific. Do you get an error? If so, what is the error?

Posted 27-Jan-11 10:31am

leckey