none
Add data to Sharepoint 2013 list from Excel with VBA RRS feed

  • Question

  • I have been attempting to add data to a Sharepoint 2013 list from Excel using VBA with no success. I am using as a template the example that is found all over the internet.

    Sub Add_Item(ListName As String, SharepointUrl As String, ValueVar As String, FieldNameVar As String)
    
    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='3' 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/UpdateListItems"
    
    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

    Since I am new to using XML and SOAP I decided to work backwards and just try to add an item to the list first. I don't have an issue with getting the data from the sheet. I don't pass any parameters into the sub, I just assign values.

    I don't get any errors. But no data gets written to the list, either. I even get a status of 200 - which simply means "OK".
    I even tried taking some characters out of the list name or the url to see if that would give an error, but I still get a status of 200.

    If I use Msxml2.ServerXMLHTTP instead of Msxml2.XMLHTTP I get a status of 401. Is there any way I can force an error so I can see what I need to correct to get this to work?

    Here is what I am working with (hopefully I removed any sensitive information):

    Sub Add_Item()
    
    ' Hard coding the parameters to start so just testing the output to SharePoint
    Dim ListName As String
    Dim SharepointUrl As String
    Dim Branch_Cost_Center As String
    Dim Field1NameVar
    ListName = "Nightly Device Tracking Excel XML Test"
    SharepointUrl = "https://{myurl}"
    Field1NameVar = "Branch Cost Center"
    Branch_Cost_Center = "919191"
    ' Remove the above when passing parameters
    
    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='3' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + Field1NameVar + ">" + Branch_Cost_Center + "</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/UpdateListItems"
    
    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>"
    
    'for debugging
    Debug.Print strSoapBody
    
    objXMLHTTP.send strSoapBody
    
    Debug.Print objXMLHTTP.Status
    
    If objXMLHTTP.Status = 200 Then
    ' Do something with response
    End If
    
    Set objXMLHTTP = Nothing
    
    End Sub

    Wednesday, March 1, 2017 7:18 PM

All replies

  • Hi,

    Here is my test code for your reference.

     Dim objXMLHTTP As MSXML2.XMLHTTP
    
            Dim strListNameOrGuid As String
            Dim strBatchXml As String
            Dim strSoapBody As String
            Dim ListName As String
            Dim SharepointUrl As String
            Dim ValueVar As String
            Dim FieldNameVar As String
    
            ListName = "Parent Name"
            SharepointUrl = "http://sp:12001/"
            ValueVar = "MyTitle"
            FieldNameVar = "Title"
    
            objXMLHTTP = New MSXML2.XMLHTTP
    
            strListNameOrGuid = ListName
    
    
            'Add New Item'
            strBatchXml = "<Batch OnError='Continue'><Method ID='1' 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/UpdateListItems")
    
            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
                Debug.Print(objXMLHTTP.status)
            End If
    
            objXMLHTTP = Nothing

    Here are some tips for you notice if error:

    1.      Try to use field static name instead of display name.

    2.      Follow the guideline below to construct your request xml

                   https://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems(v=office.12).aspx

    3.      Try to debug the objXMLHTTP object so you could get more details information.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, March 2, 2017 8:52 AM
  • Thank you Lee. I will reply and mark when I get a chance to work with this.
    Thursday, March 2, 2017 8:36 PM
  • Hi,

    Could you provide any feedback for your issue?

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, March 6, 2017 10:15 AM
  • Lee,

    I do not see much difference in the code you listed. With the exception that you have () around the parameters for the xmlhttp methods. When I use them, I get errors.

    I tried using the internal name for the field "Branch_x0020_Cost_x0020_Center" vs.
     "Branch Cost Center"

    and the GUID rather than the list name.

    I still get no errors. A return of 200.

    I was going to include a picture of the watch on the xmlhttp object, but am not authorized to upload images.

    The responseText is
    : responseText : "
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns:o="urn:schemas-microsoft-com:office:office" lang=""

    Tuesday, March 7, 2017 1:22 AM
  • Hi,

    I list the difference below so you could try again.

    Your code:

    <Field Name=" + Field1NameVar + ">" + Branch_Cost_Center + "</Field>

    My test code:

    <Field Name='" + FieldNameVar + "'>" + ValueVar + "</Field>

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, March 7, 2017 1:29 AM
  • I had wondered about the quotes around a string. I tried what you showed. I even tried using this:
    Dim DoubleQuote As String
    DoubleQuote = Chr(34)

    <Field Name=" & DoubleQuote & Field1NameVar & DoubleQuote & ">" & Branch_Cost_Center & "</Field>

    None of that makes a difference.

    I am trying to learn how to assign the return value of the soap call UpdateListItemsResult UpdateListItemsResponse so I can see what is going on, but have not been able to do that. It would be nice if I got some sort of error to tell me what is NOT happening so I can figure out how to make it happen. It all appears correct.

    Wednesday, March 8, 2017 7:20 PM
  • Hi,

    Could you hardcode the field name and check(<Field Name='Branch_x0020_Cost_x0020_Center'>" + Branch_Cost_Center + "</Field>), the right format should be like this("<Field Name='ID'>4</Field>"), as you use a variable here, so the dev code should be like( <Field Name='" + FieldNameVar + "'>" + ValueVar + "</Field>).

    The return code 200 means it’s a success request, but there should be issue when field parsing which caused update failure.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, March 9, 2017 1:50 AM
  • Hi,

    I am checking to see how things are going there on this issue. Please let us know if you would like further assistance.

    You can mark the helpful post as answer to help other community members find the helpful information quickly.

    You can also share your own solution here and mark it as answer, and we can learn from each other.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, March 13, 2017 8:56 AM
  • Lee,

    I certainly appreciate the assistance. At this point, I don't even know what to ask.

    I thought I had found out why this was not working. I had not noticed there was a "Title" column that was set to required. The List had been built from scratch, so the Title column was automatic. I thought that just adding data to one column and not putting anything in the required column was the issue with nothing being written. I deleted my "Branch Cost Center" column and renamed "Title" to "Branch Cost Center" and set it to not required. Still nothing.

    How can I check to see if there is an error when field parsing?

    I've got to think it is either the field parsing or the XML setup not opening the file properly in the first place:
    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/UpdateListItems"

    Here are various soap strings that get passed in the send with different ways I tried to set up the xml string. They all return a 200.

    <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>"{B43D3169-23C9-400F-A0FC-D376F7ECF84B}"</listName>
    <updates><Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name="Branch Cost Center">919191</Field></Method></Batch></updates>
    </UpdateListItems>
    </soap:Body>
    </soap:Envelope>
    

    <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>"B43D3169-23C9-400F-A0FC-D376F7ECF84B"</listName>
    <updates><Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name="Branch Cost Center">919191</Field></Method></Batch></updates>
    </UpdateListItems>
    </soap:Body>
    </soap:Envelope>
    

    <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>"Nightly Device Tracking Excel XML Test"</listName>
    <updates><Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name="Title">919191</Field></Method></Batch></updates>
    </UpdateListItems>
    </soap:Body>
    </soap:Envelope>
    
    

    <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>"Nightly Device Tracking Excel XML Test"</listName>
    <updates><Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name="Branch Cost Center">919191</Field></Method></Batch></updates>
    </UpdateListItems>
    </soap:Body>
    </soap:Envelope>

    Monday, March 13, 2017 9:52 PM
  • Hi,

    You’re still using <Field Name="Title">, try to use <Field Name=’Title’>  and check.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, March 22, 2017 9:54 AM