none
VBA and sharepoint RRS feed

  • Question

    1. The following code is not producing encouraging results

    Function checkOut()

    Filename = "http://www.mysite.net/sites/BT%20Daily%20Checks/" & y & m & "-%20BT%20Daily%20Checks.xlsx"

    If Workbooks.CanCheckOut(Filename) Then Workbooks.checkOut (Filename)

    End Function

    1. The code runs fine - no errors - but when I go to check the file in SharePoint - the file is NOT checked out

    1. Furthermore - if I add the following code

    Dim xlApp As Excel.Application

    Dim wb As Workbook

    Filename =  "http://www.mysite.net/sites/BT%20Daily%20Checks/" & y & m & "-%20BT%20Daily%20Checks.xlsx"

    If Workbooks.CanCheckOut(Filename) Then Workbooks.checkOut (Filename)

    Set xlApp = New Excel.Application

    xlApp.Visible = True

    Set wb = xlApp.Workbooks.Open(Filename, , False)

    I get this:

    1. Also the Workbook opens in viewer and then want’s to be saved as a different file.

    1. What  I need is a way of editing THAT file in the SharePoint server

    Ideas ?



    God bless you all :)

    Wednesday, October 26, 2016 4:35 PM

All replies

  • I'm not sure I fully understand your question, but I think you want something like this.

    Sub CheckOutFiles()
    
    'If nobody has the file checked out
    If Workbooks.CanCheckOut("http://your_url_here/ExcelList.xlsb") = True Then
    Application.DisplayAlerts = False
    
    'Open the file on the SharePoint server
    Workbooks.Open Filename:="http://your_url_here/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever
    
    
    ActiveSheet.Cells(2, 7).Value = 100
    ActiveSheet.Cells(3, 7).Value = 200
    ActiveSheet.Cells(4, 7).Value = 300
    
    
    'Close the workbook
    Workbooks("ExcelList.xlsb").Save
    Workbooks("ExcelList.xlsb").Close
    
    End If
    End Sub

    Or, this.

    Sub OpenAndCloseWBFromSharePointFolder()

    'If nobody has the file checked out
    If Workbooks.CanCheckOut("http://your_url_here/ExcelList.xlsb") = True Then
    Application.DisplayAlerts = False

    'Open the file on the SharePoint server
    Workbooks.Open Filename:="http://your_url_here/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://your_url_here/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://your_url_here/ExcelList.xlsb"

    'Close the workbook
    Workbooks("ExcelList.xlsb").Close
    End If
    End If

    End Sub


    MY BOOK

    Thursday, November 10, 2016 5:28 PM