What arguments does UpdateListItems take when calling Sharepoint web service from VBA?

Question

I'm trying to add an item to a Sharepoint list using VBA. I don't want the user to have to install anything, so I'm just using Microsoft Soap Type Library. Code as follows:

Sub test()

Dim soap As MSSOAPLib.SoapClient
Dim XMLstr As String
Dim listid As String
Dim listname As String

Set soap = New SoapClient
Call soap.mssoapinit(bstrwsdlfile:="http://wss/mySharepointSite/_vti_bin/lists.asmx?wsdl")


listid = "{e285aa1a-my-list-ID-d446cdbf091e}"
listname = "thisList"

XMLstr = "<Method ID='1' Cmd='New'>" & _
            "<Field Name='ID'>New</Field>" & _
            "<Field Name='personID'>1337</Field>" & _
        "</Method>"

soap.UpdateListItems listid, XMLstr

End Sub

I keep getting a "Type Mismatch" error on the soap.UpdateListItems line, regardless of whether I use listid or listname as the first parameter. I tried reading the WSDL to determine what type of parameter should be passed, but I don't understand it. What should I be passing here?

EDIT: I got it to work by using Microsoft Soap Type Library 3.0 instead, changing MSSOAPLib.SoapClient->MSSOAPLib30.SoapClient30 and bstrwsdlfile->par_wsdlfile, and surrounding XMLstr with:

<Batch OnError='continue' ListVersion='1' ViewName='" & ListView & "'>

...

</Batch>

Still trying to work out a way to do this without requiring users to install MSSoap 3.0.

Solution

I solved this by sending the XML as an HTTP POST, submitted via MSXML2.XMLHTTP. Code as follows:

Function updateSharePointList(listURL as string, list As String) As DOMDocument

Dim xmlhtp As New MSXML2.XMLHTTP
Dim XMLDOC As New DOMDocument
Dim xmlstr as String

xmlstr = "<?xml version=""1.0"" encoding=""utf-8""?>" & _
"<soap12:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap12=""http://www.w3.org/2003/05/soap-envelope"">" & _
  "<soap12:Body>" & _
    "<UpdateListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
      "<listName>" & list & "</listName>" & _
      "<updates>" & _
        "<Batch OnError='continue' ListVersion='1'>" & _
        "<Method ID='1' Cmd='New'>" & _
            "<Field Name='ID'>New</Field>" & _
            'all of your field updates go here, e.g.: 
            "<Field Name='userID'>1337</Field>" & _
            "<Field Name='comment'>first!</Field>" & _
        "</Method>" & _
        "</Batch>" & _
        "</updates>" & _
    "</UpdateListItems>" & _
  "</soap12:Body>" & _
"</soap12:Envelope>"

With xmlhtp
        .Open "POST", listURL, False
        .setRequestHeader "Host", "wss"
        .setRequestHeader "Content-Type", "application/soap+xml; charset=utf-8"

        .send xmlstr

        XMLDOC.LoadXML .responseText
        Set updateSharePointList = XMLDOC

End With

End Function

This requires a reference to Microsoft XML (I used "Microsoft XML, v6.0"), which AFAIK is in any standard set of VBA references. No DLL registration is needed. The function returns a DOMDocument with the result XML returned by UpdateListItems, which you can parse to do error checking.

Comments

[sigil]

Why? It's included on the MSDN page on UpdateListItems. I thought it's necessary for generating a new autonumber.

[Drkawashima]

Does anyone know the significance of the ID-field when using the New command? Like you say, it is included in the MSDN example- but it's not explained why and if it's actually required. Doesn't actually seem be required - I've run some tests and everything works fine without it.

[Drkawashima]

Foiund the answer to my own comment: The ID-field is ignored in the New Command. Check msdn.microsoft.com/en-us/library/cc313068(v=office.12).aspx. It says in section 3.1.4.31.2.1 that <Field Name="ID"> MUST be ignored in a 'New'. So it doesn't seem to be required.