none
Update sharepoint list metadata using REST from VBA (using MSXML2.XMLHTTP60) RRS feed

  • Question

  • I'm using REST from VBA (using MSXML2.XMLHTTP60) which is working flawless for reading but not for writing metadata. The FormDigestValue is correct (see below).

    Code:

    Dim oHttp  As New MSXML2.XMLHTTP60
    Dim sURL As String
    Dim sBody As String
    Dim RequestDigest As String

    RequestDigest = GetDigest("https://XXXX.sharepoint.com/sites/test")

    sURL = "https://XXXX.sharepoint.com/sites/test/_api/web/lists/GetByTitle('TestList_VBA')/items(1)" sBody = "{ '__metadata': { 'type': 'SP.Data.TestList_x005f_VBAListItem' }, 'Finished': true }" With oHttp .Open "POST", sURL, False .setRequestHeader "accept", "application/atom+xml" .setRequestHeader "X-HTTP-Method", "MERGE" .setRequestHeader "IF-MATCH", "*" .setRequestHeader "Content-Type", "application/json;odata=verbose" .setRequestHeader "X-RequestDigest", RequestDigest .setRequestHeader "Content-Length", Len(sBody) .send sBody End With


    .send sBody results in runtime error '-2147467260 (80004004)' and oHttp.responseText is empty


    Nevertheless the value

    Finished = true

    is set correctly in the sharepoint list, so this seems to be no syntax error.

    The function to get the RequestDigest:


    Private Function GetDigest(url As String) Dim oHttp As new MSXML2.XMLHTTP60 Dim s As String Dim l1 As Long Dim l2 As Long

    With oHttp .Open "POST", url + "/_api/contextinfo", False .setRequestHeader "content-type", "application/json;odata=verbose" .send ""
    End With s = oHttp.responseText l1 = InStr(1, s, "FormDigestValue") If l1 > 10 Then l1 = l1 + 16 l2 = InStr(l1, s, "</d:FormDigestValue") End If If l2 > 10 Then GetDigest = Mid$(s, l1, l2 - l1) Set oHttp = Nothing End Function


    which does basically the same (except the empty body string) works without any problems.

    Of cause it is possible to use 'On error resume next', but this is at least a bad style...


    • Edited by DO5HSK Monday, March 27, 2017 8:47 AM Typing error
    Friday, March 24, 2017 2:54 PM

All replies

  • Hi,

    Seems you’re using SharePoint online, make sure the FormDigestValue was generated and set correctly, for building FormDigestValue ,here are some links for your reference.

    https://blog.sprider.org/2016/09/15/access-sharepoint-online-rest-api-via-google-postman-with-user-context/

    https://finarne.wordpress.com/2015/06/10/403-forbidden-from-_apicontextinfo-when-using-chrome-postman-rest-app/

    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 27, 2017 6:50 AM
  • Thanks for your answer, but that's not the problem. Getting and using the correct FormDigestValue is done in the Function GetDigest(url As String) which is working without any problems. If I use the wrong (or none) FormDigestValue, I do not get any runtime error, but a 'normal' error response (...The security validation for this page is invalid and might be corrupted...).

    Using the correct FormDigestValue the .send method results in the mentioned runtime error and I don't get any response from the HTTP request. Nevertheless the value is set correctly in the sharepoint list.

    Monday, March 27, 2017 8:44 AM
  • Hi,

    I just create a console VB project to debug your code, and found there’re some issue, so I would suggest you debug your code and confirm the logic is correct.

    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 27, 2017 9:29 AM
  • Hi,

    Thanks again for your help. But as already mentioned: The Function GetDigest(url As String) is NOT the problem. No idea what the error message in your example means, in VBA (Excel) there is no error. Maybe the syntax of VBA and VB.NET (probably your console VB project is VB.NET) is not 100% the same.

    The full .response in the GetDigest function is:

    <?xml version="1.0" encoding="utf-8"?>
    <d:GetContextWebInformation xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
    xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
    xmlns:georss="http://www.georss.org/georss" 
    xmlns:gml="http://www.opengis.net/gml" 
    m:type="SP.ContextWebInformation">
    <d:FormDigestTimeoutSeconds m:type="Edm.Int32">1800</d:FormDigestTimeoutSeconds>
    <d:FormDigestValue>0x1E69D7C63D580B8ECC92F40A0B5DAF5B4EB903066DDC6A1F81DF8980816B934BB5CBD79F12D8AE56E264E14FE141B62BEA316C99CC7975442C10B953750B0FBD,27 Mar 2017 09:44:56 -0000</d:FormDigestValue>
    <d:LibraryVersion>16.0.6316.1207</d:LibraryVersion>
    <d:SiteFullUrl>https://XXXX.sharepoint.com/sites/test</d:SiteFullUrl>
    <d:SupportedSchemaVersions m:type="Collection(Edm.String)">
    <d:element>14.0.0.0</d:element>
    <d:element>15.0.0.0</d:element>
    </d:SupportedSchemaVersions><d:WebFullUrl>https://XXXX.sharepoint.com/sites/test</d:WebFullUrl></d:GetContextWebInformation>
    


    The final RequestDigest is:

    0x1E69D7C63D580B8ECC92F40A0B5DAF5B4EB903066DDC6A1F81DF8980816B934BB5CBD79F12D8AE56E264E14FE141B62BEA316C99CC7975442C10B953750B0FBD,27 Mar 2017 09:44:56 -0000



    In your error message the function InStr uses Integer as start. In VBA also Long is allowed. Just use this:

    Private Function GetDigest(url As String)
      Dim oHttp As new MSXML2.XMLHTTP60
      Dim s As String
      Dim l1 As Integer
      Dim l2 As Integer
    
    With oHttp
      .Open "POST", url + "/_api/contextinfo", False
      .setRequestHeader "content-type", "application/json;odata=verbose"
      .send ""
    End With
      s = oHttp.responseText
      l1 = InStr(1, s, "FormDigestValue")
      If l1 > 10 Then
        l1 = l1 + 16
        l2 = InStr(l1, s, "</d:FormDigestValue")
      End If
      If l2 > 10 Then GetDigest = Mid$(s, l1, l2 - l1)
      Set oHttp = Nothing
    End Function
    I think, the above mentioned error (overload resolution failed... ) should vanish then.


    • Edited by DO5HSK Monday, March 27, 2017 10:01 AM
    Monday, March 27, 2017 9:40 AM
  • Just to eliminate the possibility that sending a JSON string with MSXML2.XMLHTTP60 is the problem, I tried this code:

    Sub Example_REST_Check1()
    
    Dim oHttp As New MSXML2.XMLHTTP60
    Dim sURL As String
    Dim sBody As String
    
    sURL = "https://gurujsonrpc.appspot.com/guru"
    sBody = "{ 'method' : 'guru.test', 'params' : [ 'Guru' ], 'id' : 123 }"
    
    With oHttp
        .Open "POST", sURL, False
        .setRequestHeader "accept", "application/json;odata=verbose"
        .setRequestHeader "Content-Type", "application/json;odata=verbose"
        .send sBody
    End With
    
    MsgBox oHttp.responseText 'result: {"jsonrpc":"2.0"‚"id":123‚"result":"Hello Guru!"}
    
    End Sub

    Works flawless. No error with .send

    Just out of curiosity I tried the code from my first post in VB.NET : Same error as with VBA. I can post the complete code here if this would help.


    • Edited by DO5HSK Wednesday, March 29, 2017 10:10 AM
    Wednesday, March 29, 2017 10:09 AM
  • Dear DO5HSK,

    I am struggling to achieve a simple list update into SharePoint (online - Office 365) using VBA (from Outlook, but I expect  this shouldn't differ markedly from using Excel in this regard).

    It seems you have more experience in this field. Are you able to provide any pointers to resources you've found useful in your journey thus far?

    Or specifically, if I simply want to update the value (text) in the "Title" field (list column), I presume I could use your above code with only a need to modify the value of sBody (plus URL parameters etc). Can you give an example of an sBody that would achieve this?

    I tried :


    sBody = "{""Title"":""My New Title""}"

    which yields a .ResponseText of

    <?xml version="1.0" encoding="utf-8"?><m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><m:code>-1, Microsoft.SharePoint.Client.InvalidClientQueryException</m:code><m:message xml:lang="en-US">An entry without a type name was found, but no expected type was specified. To allow entries without type information, the expected type must also be specified when the model is specified.</m:message></m:error>

    I feel this is the closest I've gotten yet in my quest, but can find very little on-line to assist...

    Kind regards

    John.

    Wednesday, March 29, 2017 1:14 PM
  • Hi John,

    No problem. Except the unfortunate run time error.

    Two things are probably to be considered:
    1. You need the correct name of your listitem(s). In my case this was 'TestList_x005f_VBAListItem'. You can find it in the .response XML in the tag: 'term' if you just read the item.
    ... <category scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" term="SP.Data.TestList_x005f_VBAListItem"/>...

    Or read the metadata of your list (sURL=http://site url/_api/web/lists/GetByTitle(‘MyList') ) and find the name as:
     <d:ListItemEntityTypeFullName>SP.Data.MyListItemName</d:ListItemEntityTypeFullName>

    This name is required as 'type' in the body to update the item
    2. The sBody to update anything always starts with __metadata . So your sBody should be something like:
    sBody = "{ '__metadata': { 'type': 'SP.Data.TestList_x005f_VBAListItem' }, 'Title': 'My New Title' }"

    My knowledge is just from the official Sharepoint REST Docu:
    https://msdn.microsoft.com/en-us/en-enlibrary/office/dn292552.aspx




    • Edited by DO5HSK Wednesday, March 29, 2017 4:02 PM
    Wednesday, March 29, 2017 3:17 PM
  • DO5HSK,

    Thank you so much for your assistance!  I have now got it 'working' except, when it actually succeeds, it always throws an error 'Operation Aborted' on the .send line. If I (deliberately or otherwise) muck up a parameter I don't get this error, and I usually can query the .ResponseText to determine what I mucked-up...  Strange that it throws this error when it actually works (i.e. the target field of the SharePoint List Item correctly gets updated).

    Anyhow, I modified your GetDigest Code a little to use XML rather than string parsing, and include that along with my full code below.

    Option Explicit 'Required References 'Microsoft XML, V6.0 Const mconszModuleName As String = "modSPListUpdate" Private mSzMsgPrompt As String Private mSzMsgTitle As String Sub TestIt() UpDateSPListItem "https://mydomain.sharepoint.com", "My Test List", "Title", 1, "My New Title" End Sub Sub UpDateSPListItem(szBaseURL As String, szListTitle As String, szFieldTitle As String, lngListItemID As Long, szNewValue As String) Const conszProcedureName As String = "UpDateSPListItem" On Error GoTo UpDateSPListItem_ERR 'Object variable declarations Dim oHttp As New MSXML2.XMLHTTP60 Dim objXML As MSXML2.DOMDocument Dim xNode As IXMLDOMNode 'Non-Object variable declarations Dim szRequestDigest As String Dim szURL As String Dim szBody As String Dim szCheckResult As String Dim szListItemEntityTypeFullName As String Const conszNewValue As String = "My new field value" Const conlngID As Long = 60 'the ID# of the list Item - constant for dev/testing 'Body szRequestDigest = GetDigest(szBaseURL) szListItemEntityTypeFullName = szGetListItemEntityTypeFullName(szBaseURL, szListTitle) szURL = szBaseURL & "/_api/web/lists/GetByTitle('" & szListTitle & "')/items(" & lngListItemID & ")" szBody = "{ '__metadata': { 'type': '" & szListItemEntityTypeFullName & "' }, '" & szFieldTitle & "': '" & szNewValue & "' }" With oHttp .Open "POST", szURL, False .setRequestHeader "accept", "application/atom+xml" .setRequestHeader "X-HTTP-Method", "MERGE" .setRequestHeader "IF-MATCH", "*" .setRequestHeader "Content-Type", "application/json;odata=verbose" .setRequestHeader "X-RequestDigest", szRequestDigest .setRequestHeader "Content-Length", Len(szBody) On Error Resume Next .Send szBody 'The above line always throws an error -2147467260 'Operation Aborted' when it (actually) succeeds! Err.Clear On Error GoTo UpDateSPListItem_ERR Do Until oHttp.readyState = 4 DoEvents Loop If .Status = 1223 Then 'This (1223) is the status code that accompanies the -2147467260 'Operation Aborted' error 'Verify that the update did, in-fact, succeed... .Open "GET", szURL, False .Send Do Until oHttp.readyState = 4 DoEvents Loop If .Status <> 200 Then Err.Raise vbObjectError + .Status, , "Server Error: " & .Status & " - " & .statusText End If Set objXML = New MSXML2.DOMDocument If Not objXML.LoadXML(oHttp.responseText) Then Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason End If Set xNode = objXML.getElementsByTagName("d:" & szFieldTitle)(0) If Not xNode Is Nothing Then szCheckResult = xNode.nodeTypedValue If szCheckResult <> szNewValue Then Err.Raise vbObjectError + 1001, conszProcedureName, "SharePoint List Update Failed" End If End If ElseIf .Status <> 200 Then Err.Raise vbObjectError + .Status, , "Server Error: " & .Status & " - " & .statusText End If End With UpDateSPListItem_EXIT: On Error Resume Next 'Release relevant object level variables Set oHttp = Nothing 'As New MSXML2.XMLHTTP60 Set objXML = Nothing 'As MSXML2.DOMDocument Set xNode = Nothing 'As IXMLDOMNode Exit Sub UpDateSPListItem_ERR: mSzMsgTitle = "Error" mSzMsgPrompt = "An unexpected error has occurred in " & mconszModuleName & "." & conszProcedureName & "." _ & vbCrLf & vbCrLf & "Error Number : " & Err.Number _ & vbCrLf & "Error Description : " & Err.Description _ & vbCrLf & "Error Source : " & Err.Source MsgBox mSzMsgPrompt, vbExclamation + vbOKOnly, mSzMsgTitle Resume UpDateSPListItem_EXIT 'The next two lines are present for use during debug Resume Next Resume End Sub Function szGetListItemEntityTypeFullName(szSiteURL As String, szListTitle As String) As String Const conszProcedureName As String = "szGetListItemEntityTypeFullName" On Error GoTo szGetListItemEntityTypeFullName_ERR 'Object variable declarations Dim oHttp As New MSXML2.XMLHTTP60 Dim objXML As MSXML2.DOMDocument Dim xNode As IXMLDOMNode 'Non-Object variable declarations Dim szURL As String 'Body szURL = szSiteURL & "/_api/web/lists/GetByTitle('" & szListTitle & "')?$select=ListItemEntityTypeFullName" With oHttp .Open "GET", szURL, False .Send Do Until oHttp.readyState = 4 DoEvents Loop If .Status <> 200 Then Err.Raise vbObjectError + .Status, , "Server Error: " & .Status & " - " & .statusText End If Set objXML = New MSXML2.DOMDocument If Not objXML.LoadXML(oHttp.responseText) Then Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason End If Set xNode = objXML.getElementsByTagName("d:ListItemEntityTypeFullName")(0) If Not xNode Is Nothing Then szGetListItemEntityTypeFullName = xNode.nodeTypedValue End With szGetListItemEntityTypeFullName_EXIT: On Error Resume Next 'Release relevant object level variables Set oHttp = Nothing 'As New MSXML2.XMLHTTP60 Set objXML = Nothing 'As MSXML2.DOMDocument Set xNode = Nothing 'As IXMLDOMNode Exit Function szGetListItemEntityTypeFullName_ERR: mSzMsgTitle = "Error" mSzMsgPrompt = "An unexpected error has occurred in " & mconszModuleName & "." & conszProcedureName & "." _ & vbCrLf & vbCrLf & "Error Number : " & Err.Number _ & vbCrLf & "Error Description : " & Err.Description _ & vbCrLf & "Error Source : " & Err.Source MsgBox mSzMsgPrompt, vbExclamation + vbOKOnly, mSzMsgTitle Resume szGetListItemEntityTypeFullName_EXIT 'The next two lines are present for use during debug Resume Next Resume End Function

    Private Function GetDigest(url As String) Const conszProcedureName As String = "GetDigest" On Error GoTo GetDigest_ERR 'Object variable declarations Dim oHttp As New MSXML2.XMLHTTP60 Dim objXML As MSXML2.DOMDocument Dim xNode As IXMLDOMNode 'Non-Object variable declarations 'Body With oHttp .Open "POST", url + "/_api/contextinfo", False .setRequestHeader "content-type", "application/json;odata=verbose" .Send "" Do Until .readyState = 4 DoEvents Loop If .Status <> 200 Then Err.Raise vbObjectError + .Status, , "Server Error: " & .Status & " - " & .statusText End If End With Set objXML = New MSXML2.DOMDocument If Not objXML.LoadXML(oHttp.responseText) Then Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason End If Set xNode = objXML.getElementsByTagName("d:FormDigestValue")(0) If Not xNode Is Nothing Then GetDigest = xNode.nodeTypedValue GetDigest_EXIT: On Error Resume Next 'Release relevant object level variables Set xNode = Nothing Set objXML = Nothing Set oHttp = Nothing Exit Function GetDigest_ERR: mSzMsgTitle = "Error" mSzMsgPrompt = "An unexpected error has occurred in " & mconszModuleName & "." & conszProcedureName & "." _ & vbCrLf & vbCrLf & "Error Number : " & Err.Number _ & vbCrLf & "Error Description : " & Err.Description _ & vbCrLf & "Error Source : " & Err.Source MsgBox mSzMsgPrompt, vbExclamation + vbOKOnly, mSzMsgTitle Resume GetDigest_EXIT 'The next two lines are present for use during debug Resume Next Resume End Function

    Note : I don't intend to use the call to the 'szGetListItemEntityTypeFullName' function in production code, however it might be useful to someone, so I've left it in the above too.  I'll just hard code in the return value, once known for a list that I want to work with.

    Again.  Thanks for your assistance.

    If you can shed any light on , or offer a better way to handle, the Operation Aborted issue; I'm all ears.

    Kind regards,

    John

    Thursday, March 30, 2017 1:53 PM
  • ...I have now got it 'working' except, when it actually succeeds, it always throws an error 'Operation Aborted' on the .send line....

    That's exactly the cause why I started this thread!

    Thursday, March 30, 2017 2:14 PM
  • ...I did realise that, a little after I posted my verbose response.

    Anyway, if you (or anyone else!) can shed any light....

    Friday, March 31, 2017 2:48 AM
  • I am having the exact same issue. Using MSXML2 from within a C# console app. The update is successful, but the COM object throws an error:

    System.Runtime.InteropServices.COMException: 'Operation aborted (Exception from HRESULT: 0x80004004 (E_ABORT))' 

    Error code: -2147467260

    Wednesday, January 3, 2018 12:24 PM