none
Cannot check out document from SharePoint with VBA RRS feed

  • Question

  • Hi

    I have an macro-enabled workbook and need to transfer data to another workbook that is stored on SharePoint. I found the following code to update the workbook, however it always tells me I cannot check out this document.

    Private Sub Test ()
    
        Dim Target_Workbook As Workbook
        Dim Target_Path As String
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
                    
        Target_Path = "//sharepoint/folder/file name.xlsx"
        If Workbooks.CanCheckOut(Target_Path) = True Then
            Set Target_Workbook = Workbooks.Open(Target_Path)
            Workbooks.CheckOut Target_Path
            
        Else
            MsgBox ("File on Sharepoint can NOT be checked out." + Chr(13) + _
                    "Make sure no one else is working in the file." + Chr(13) + _
                    "Including yourself.")
            Exit Sub
        End If
    
    '*****************
    
    'update document
    
    '*****************
    
        Target_Workbook.CheckInWithVersion True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    
    End Sub
    
    
    
    

    I checked my rights on SharePoint and I have sufficient permission to add/update a document. Also I double checked if this document is already in use but it is check in at any time. Any idea what could be casing the error, that I am not able to checkout the document?

    Many thanks
    Rob


    • Edited by Rob_10 Friday, June 24, 2016 9:48 AM
    Friday, June 24, 2016 9:29 AM

All replies

  • Try this:

    Sub testing()
        Dim docCheckOut As String
        'docCheckOut = "//office.bt.com/sites/Training/Design Admin/Training Plan/adamsmacro.xlsm"
        docCheckOut = "http://excel-pc:43231/Shared Documents/ExcelList.xlsb"
        Call UseCheckOut(docCheckOut)
    End Sub
    
    Sub UseCheckOut(docCheckOut As String)
         ' Determine if workbook can be checked out.
        If Workbooks.CanCheckOut(docCheckOut) = True Then
            Workbooks.CheckOut docCheckOut
        Else
            MsgBox "Unable to check out this document at this time."
        End If
    End Sub
    


    Give this a go too:

    'http://excel-pc:43231/Shared Documents/
    'http://excel-pc:43231/Shared%20Documents/Forms/AllItems.aspx
    
    
    Sub OpenAndCloseWBFromSharePointFolder()
    
    'If nobody has the file checked out
    If Workbooks.CanCheckOut("http://excel-pc:43231/Shared Documents/ExcelList.xlsb") = True Then
    Application.DisplayAlerts = False
    
    'Open the file on the SharePoint server
    Workbooks.Open Filename:="http://excel-pc:43231/Shared Documents/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever
    
    'Close the workbook
    Workbooks("ExcelList.xlsb").Close
    Application.DisplayAlerts = True
    Else
    Application.DisplayAlerts = False
    
    'Open the File to check if you already have it checked out
    Workbooks.Open Filename:="http://excel-pc:43231/Shared Documents/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever
    
    'See if doc can be checked in
    If Application.Workbooks("ExcelList.xlsb").CanCheckIn Then
    
    'Check In, Save and Close
    Application.Workbooks("ExcelList.xlsb").CheckIn SaveChanges:=True, Comments:="Checked-In before Delete"
    
    'Open the file again
    Workbooks.Open Filename:="http://excel-pc:43231/Shared Documents/ExcelList.xlsb"
    
    'Close the workbook
    Workbooks("ExcelList.xlsb").Close
    End If
    End If
    
    End Sub
    

     

    MY BOOK

    Monday, July 4, 2016 2:41 PM