none
Update list in SharePoint from Excel (office 365) RRS feed

  • Question

  • Hi all,

    I'm having a lot of difficulties doing this. I have a list in SharePoint, and I need to update it from a spreadsheet in Excel. I can export the list to Excel, but as you know, office doesn't let me update any items directly from excel (I know that there was some add in which lets you do that, but that doesn't seem to work anymore).

    I can link it in Microsoft Access, and update it in Access, but I really need to be in Excel. So i thought about linking Excel to my Access database, since Access is linked to my Sharepoint List, but that also didn't work. Now I'm trying to create a macro in Excel, that when I execute, it changes the database in Access, and after that, Access can update the Sharepoint List. But that seems too complicated, since I'm relativity in VBA programming. Is there any way I can do this, update a list in Sharepoint using an Excel SpreadSheet ?? Is it possible using VBA ?? Or does Office 365 provide any tool to help me on that ??

    Thanks, Igor.

    Friday, February 24, 2017 12:58 PM

All replies

  • Hi lgor,

    >> Is there any way I can do this, update a list in Sharepoint using an Excel SpreadSheet ?? Is it possible using VBA ??

    Is the SharePoint list created based on Excel SpreadShhet? If so, the link below might be useful to you.

    # Can I update a list in SharePoint 365 using an excel shreadsheet

    https://social.msdn.microsoft.com/Forums/office/en-US/a5a8db56-4fc3-4ce9-8c8a-8d41df97a649/can-i-update-a-list-in-sharepoint-365-using-an-excel-shreadsheet?forum=sharepointgeneral

    If not, I suggest you try to refer below link. But, it is also not easy.

    # Add and Update Single Item in Sharepoint List via VBA

    http://stackoverflow.com/questions/22450717/add-and-update-single-item-in-sharepoint-list-via-vba

    # Add and Update Single Item in Sharepoint List via VBA

    http://sharepoint.stackexchange.com/questions/93181/add-and-update-single-item-in-sharepoint-list-via-vba

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 27, 2017 7:40 AM
  • I read the links you sent, but the solutions they show doesn't appear to be working on Office 365. Nothing happens when I execute the codes. I'm starting to think that the only solution would be working only with Access, or design a Visual Studio application that could help  me with that. Thanks for the help anyway !! 
    Thursday, March 2, 2017 12:39 PM
  • I haven't used SharePoint in this way for a long time, but I have some old code sitting around that may work for you.


    Private Sub CreateList()
        Dim folder As folder
        Dim f As File
        Dim fs As New FileSystemObject
        Dim RowCtr As Integer
        RowCtr = 1
        Set folder = fs.GetFolder("http://excel-pc:43231/Shared Documents/Forms/") '<=Variable Location
        For Each f In folder.Files
           Cells(RowCtr, 1).Value = f.Name
           RowCtr = RowCtr + 1
        Next f
    End Sub



    MY BOOK

    Thursday, March 2, 2017 1:11 PM
  • The problem is that I'm not trying to create a list. I'm just wanted to update some lines of it. I tried using this code (this can be found in one of the links proposed by Edward Z):

    Sub Macro4()
    
        Dim ListName As String
        Dim SharepointUrl As String
        Dim ValueVar As String
        Dim FieldNameVar As String
        
        ListName = ListNameVariable
        SharepointUrl = SharepointUrlVariable
        ValueVar = "Test"
        FieldNameVar = "Title"
        
        Dim objXMLHTTP As MSXML2.XMLHTTP
        
        Dim strListNameOrGuid As String
        Dim strBatchXml As String
        Dim strSoapBody As String
        
        Set objXMLHTTP = New MSXML2.XMLHTTP
        
        strListNameOrGuid = ListName
        
        
        'Add New Item'
        strBatchXml = "<Batch OnError='Continue'><Method ID='8' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + FieldNameVar + ">" + ValueVar + "</Field></Method></Batch>"
        
        
        objXMLHTTP.Open "POST", SharepointUrl + "_vti_bin/Lists.asmx", False
        objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
        objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/"
        
        strSoapBody = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " _
         & "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
         & "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems " _
         & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & strListNameOrGuid _
         & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"
        
         objXMLHTTP.send strSoapBody
        
        If objXMLHTTP.Status = 200 Then
        '   Do something with response
        End If
        
        Set objXMLHTTP = Nothing
    
    End Sub
    

    That executes with no error, but no modifications on the list are made.

    Regards, Igor.

    Thursday, March 2, 2017 1:30 PM
  • Hi lgor,

    For updating SharePoint List, I suggest you try Lists.UpdateListItems Method.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 10, 2017 9:59 AM
  • Hi! Were you able to solve this with excel only? I'm having the same issue...

    Oscar Marquez

    Wednesday, April 12, 2017 2:30 PM