Wednesday, 10 March 2010

Set Sharepoint meta-data from VBA using updatelist web service

For the top ten best selling Rolex watches, visit Yngoo!
I've been working on a problem trying to integrate a legacy Microsoft Office 2003 VBA application with Sharepoint 2007. In theory, I should be able to use the SOAP toolkit provided my Microsoft to talk to the Sharepoint (WSS) web services, but in practice there was very little documentation of how to do this, and I could find no resources on the internet aside from the MSDN definitions of the web services, and only a little attached to the SOAP toolkit (available here: Microsoft Office 2003 Web Services Toolkit ).

The basic problem I was trying to solve was: how do I set meta-data held on a document in Sharepoint from Office 2003 using VBA?

After much head scratching I finally solved this by making some assumptions based on the MSDN reference documentation, then using the Fiddler tool until I had worked out what I was doing wrong.  Fiddler allows you to inspect the HTTP calls and responses that you are issuing/receiving.  This is very helpful when debugging web services, and well worth looking into if you are doing anything in this area.

Anyway, through Fiddler I could see the response from the web service, including the error message.  This meant that my initial best guess on how to structure the XML was at least nearly correct, because the web service was giving me a proper error message.  Eventually I had a properly structured call and everything worked!

Remember to install the SOAP toolkit before you try this.  You will then be able to add this to your VBA project and add a web reference to your project to the WSS Lists.asmx service as you would if your using .Net.  This is all explained in the MSDN link above but drop a comment if you would like me to go into more detail.

The actual VBA code used structure the call to the web service looks like this:

'Define a new list service web service class
Dim listws As New clsws_Lists

' Set up the batch command used to set the meta data. 
' Note that FileRef is set to URL of the file you want to change
'ListVersion is from my test site, you may not need that
' First set up DOM document containing fields
Dim xmlDoc As New MSXML2.DOMDocument30
xmlDoc.async = False
Dim xmlText As String
xmlText = "<root>" + _
  "<Batch OnError='Continue' ListVersion='59' PreCalc='TRUE' xmlns=''>" + _
      "<Method ID='1' Cmd='Update'>" + _
        "<Field Name='ID' />" + _
        "<Field Name='FileRef'>http://myservername:1080/sites/mysite/shared documents/TestWebService.doc</Field>" + _
        "<Field Name='Title'>Uploaded from VBA</Field>" + _
        "<Field Name='Surname'>" + sLastName + "</Field>" + _
        "<Field Name='Forename'>" + sFirstName + "</Field>" + _
        "<Field Name='Date_x0020_of_x0020_Birth'>" + sDOB + "</Field>" + _
      "</Method>" + _
    "</Batch>" + _
xmlDoc.LoadXml (xmlText)

' This is a bit of debug that checks the XML is well formed and show you it if it is. 
If xmlDoc.parseError.ErrorCode <> 0 Then
    Dim myErr
    Set myErr = xmlDoc.parseError
    MsgBox (myErr.reason)
    MsgBox xmlDoc.XML
End If

' Set up IXMLDOMNodeList object
Dim myXMLNodeList As MSXML2.IXMLDOMNodeList
Dim root As MSXML2.IXMLDOMElement
' Now the the XML node list to the batch command we have just constructed above
Set root = xmlDoc.documentElement
Set myXMLNodeList = root.ChildNodes

' Now run the web service to update the meta-data.
' Note that the first parameter is the GUID of the list that holds the data you want to change.  I can't get this to take a list name - it only seems to wrok with a GUID.
Dim updateReturn As IXMLDOMNodeList
Set updateReturn = listws.wsm_UpdateListItems("{B768B024-8B98-4918-990A-ECE34691DBBC}", myXMLNodeList)

If you need to find out the GUID or ListVersion values for your list, use the "GetListCollection" web service and iterate through the XML it returns:

Dim listCollection As IXMLDOMNode
Set listCollection = listws.wsm_GetListCollection
' You can iterate this to find the GUID of our list
I can post further examples if anybody wants any.

*Edit* - I have written another blog post on how to find the GUID of lists in VBA here:
This simple function will return the version number and the GUID of the Sharepoint list from its name.  

You may also be interested in teh following post: :  VBA code to check in a document to Sharepoint and set meta data.


  1. Thanks Iain for your post. This is exactly what I was looking for, having gone through the same process as you.

    I'm testing your example and it's choking on the myXMLNodeList portion of the meta data update. The VB error gives me a 'Type Mismatch' and Fiddler shows a HTML 401 Unauthorized error. Any thoughts on why I'm getting this? I'm on the server, logged in with admin rights, so I'm puzzled.

    Any input, further examples, would be appreciated.

    Thanks again.

  2. Hi Gretchen, can you tell me exactly which line the code executes up to please? It's worth stepping through it with the debugger.

    Assuming that it is the web service call, then if you fire up fiddler and then run your call, you should see it reported on the left hand side of the screen (it will be the last line of the calls to the ~/_vti_bin/Lists.asmx service).

    Drag that into the right hand pane and it will show you the call, with all its XML in the top window, and the response, again in XML in the bottom window. If it is the web service call that fails, it should tell you specifically why in the XML.

    Best of luck!

  3. Further examples, please! Even a simple Access VBA to drop a file into a document library, or pulling a list into word when the document opens...

  4. Hi AncientVulture, I'm currently writing a simple example of pulling the contents of a Sharepoint list into VBA and will post it once I'm happy with it. :)

  5. Hi, were you eventually able to pull the sharepoint list into vba? I am a total noob in sharepoint but can get an idea as I can read vba.. Thanks a lot!

    1. If you just want to import a Sharepoint list, then that is pretty easyin VBA. Have a look at this:

  6. Hi Iain, thanks for your post, question, do you have an idea how will I use Sharepoint web service (lists.asmx) through Excel 2007 VBA? Thanks in advance. :)

    1. Have a look at this:
      You can also use VSTO if you have Visual Studio.

  7. This comment has been removed by the author.

  8. I used the reference to Microsoft Office SOAP Type Library v3.0 but it is unable to recognize clsws_Lists. Could you explain which reference need to be defined to use your code?