VBA code to check in a document to Sharepoint and set meta data

Carissima Gold 9 ct Yellow Gold Two-Row Diamond Cut Curb Bracelet of 21 cm/8.5-inch

 

The following VBA code will upload a file to Sharepoint 2007 using a web service, and then sest some meta data (the title) for that file in the document library.
Remember, you need to have installed the VBA Microsoft Office 2003 Web Services Toolkit and created a reference to the web service in your project before this will work. See these previous posts for more information on calling Sharepoint web services from VBA:

• VBA code to iterate through the results of GetListCollection web service from Sharepoint 2007
• Set Sharepoint meta-data from VBA using updatelist web service

' Change these values to your own
sSourceFile = "C:\mtest.doc"  ' File to upload
sTargetFile = "HTTP://my sharepoint site/Shared%20Documents/mytest.doc" ' Target site, document library and file name


' First set up DOM document containing fields
Dim xmlDoc As New MSXML2.DOMDocument30
xmlDoc.async = False

xmlText = "<root>" & _
"<Batch OnError='Continue' ListVersion='" & iVersionNumber & "' PreCalc='TRUE' xmlns=''>" & _
"<Method ID='1' Cmd='Update'>" & _
"<Field Name='ID' />" & _
"<Field Name='FileRef'>" & sTargetFile & "</Field>" & _
"<Field Name='Title'>Uploaded from VBA</Field>" & _
"</Method>" & _
"</Batch>" & _
"</root>"


xmlDoc.LoadXml (xmlText)
Debug.Print xmlText

' This bit is just for testing
If xmlDoc.parseError.errorCode <> 0 Then

Set myErr = xmlDoc.parseError
MsgBox (myErr.reason)
GoTo fnUpload_Error
Else
MsgBox xmlDoc.XML
End If

' Set up IXMLDOMNodeList
Dim myXMLNodeList As MSXML2.IXMLDOMNodeList
Dim root As MSXML2.IXMLDOMElement


Set root = xmlDoc.documentElement
Set myXMLNodeList = root.ChildNodes

' Create an array of IXMLDOMNodeList
Dim ar_Fields(1) As IXMLDOMNodeList
Set ar_Fields(0) = myXMLNodeList
'Debug.Print "ar_Fields(0) = " & ar_Fields(0)


' Now set up an array of strings to hold the URL
Dim ar_URL(1) As String
ar_URL(0) = sTargetFile

Debug.Print "ar_URL(0) = " & ar_URL(0)
' Set up the results object
Dim myresults() As struct_CopyResult

' Set up the byte array and read the source file into it
Dim ar_Stream() As Byte
ar_Stream = ReadFile(sSourceFile)


' NOW CALL WEB SERVICE
' The follwoing comes from the "Microsoft Office 2003 Web Services Toolkit":
'"ar_DestinationUrls" is an array with elements defined as String
'"ar_Fields" is an array with elements defined as IXMLDOMNodeList
'"ar_Stream" is an array with elements defined as Byte
'"ar_Results" is an array with elements defined as struct_CopyResult
'See Complex Types: Arrays in Microsoft Office 2003 Web Services Toolkit Help
'for details on implementing arrays.
documentId = copyws.wsm_CopyIntoItems(sDocumentPath, ar_URL, ar_Fields, ar_Stream, myresults)
Debug.Print "DocumentID = " & documentId

Dim updateReturn As IXMLDOMNodeList

Set updateReturn = listws.wsm_UpdateListItems(sListID, myXMLNodeList)

Dim xmlReturnDoc As New MSXML2.DOMDocument30
If (updateReturn.Length > 0) Then    
xmlReturnDoc.LoadXml (updateReturn.Item(0).XML)

Dim errorText As String
errorText = xmlReturnDoc.Text
If (errorText <> "0x00000000") Then
MsgBox ("Error: Cannot upload load file to Sharepoint." & vbCrLf & _
"     : " & errorText & vbCrLf & Err.Description & vbCrLf )

End If
End If

' Uncomment for debug information.
'MsgBox ("Return XML = " & xmlReturnDoc.XML)

I use the following VBA to read the target file in as a byte array:

Private Function ReadFile(ByVal strFileName As String, Optional ByVal lngStartPos As Long = 1, Optional ByVal lngFileSize As Long = -1) As Byte()
    Dim FilNum As Integer
    FilNum = FreeFile
    Open strFileName For Binary As #FilNum
    If lngFileSize = -1 Then
        ReDim ReadFile(LOF(FilNum) - lngStartPos)  
    Else
        ReDim ReadFile(lngFileSize - 1)
    End If
    Get #FilNum, lngStartPos, ReadFile
    Close #FilNum
End Function

VBA code to iterate through the results of GetListCollection web service from Sharepoint 2007

In a previous post I discussed how to set meta-data in Sharepoint 2007 directly from VBA.  I mentioned that the Microsoft documentation for the Sharepoint web services does not appear to be correct, insofar as you cannot address a Sharepoint "list" in the web service using its name, but rather you are forced to use the GUID.

I have now had to write a function to retrieve the GUID for the list from teh Sharepoint site.  This proved to be much harder in practice than I had expected it to be, partly at least because of the problems in working in the VBA IDE with its poor debugging functionality, partly because VBA is a much more limited language than .Net and I have got used to using all the nice constructs such as generics that make life so much easier,  and partly because I still find MSXML very "brain" hard to work with.  I don't know why I find it so hard, it just always seems that everything ends up being far more complicated than I think it should be.  I find the .Net XML model much easier to deal with.

Anyway, after playing about with XSLT and XPATH in order to extract the ID and VersionNumber attributes from the wsm_GetListCollection web service (and failing miserable), I finally decided to just iterate through the nodes and attributes till I found what I wanted.  The code below is a simple function that you can call with three parameters:  the name of the list that you need the GUID for (sListName),  a string variable in which the GUID will be placed (sListID), and an integer that the list version number will be placed (iListVersion).

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

Function GetListCollection(ByVal sListName As String, ByRef sListID As String, ByRef iListVersion As Integer)
    ' Class created by toolkit to connect to the Web service
    Dim ws As New clsws_Lists
    ' The collection is returned as an XML node list
    Dim ListCollectionNodeList As MSXML2.IXMLDOMNodeList
    'Root node of the returned list
    Dim nod As MSXML2.IXMLDOMNode
    ' Output string for the XML transformation
    Dim strOutput As String
 
    On Error GoTo GetListCollection_OnError
 
    ' Retrieve the collection of lists
    Set ListCollectionNodeList = ws.wsm_GetListCollection
 
    ' Get the root node from the list
    Set nod = ListCollectionNodeList.Item(0)
 
 
    ' Iterate nodelist to find details for our named list
    Dim bGotNode
    bGotNode = False
 
 
    Dim procNode As MSXML2.IXMLDOMNode
    Dim TitleNodeList As MSXML2.IXMLDOMNodeList
    Dim IDNodeList As MSXML2.IXMLDOMNodeList
    Set TitleNodeList = nod.SelectSingleNode("//Title")
    Set IDNodeList = nod.SelectSingleNode("//ID")
 
    Dim ListNode As MSXML2.IXMLDOMNode
    Dim ListChildNodes As MSXML2.IXMLDOMNode
    Dim oAttr As MSXML2.IXMLDOMAttribute
 
    Dim retID As String
    Dim listVersion As Integer
    Dim ListName As String
 
    ' Iterate through list nodes returned (should only be one)
    For Each ListNode In ListCollectionNodeList
        'Iterate through the child nodes (each one represents a single Sharepoint list)
        For Each ListChildNodes In ListNode.ChildNodes
            'See if we have any attributes (we should have!)
            If ListChildNodes.Attributes.Length > 0 Then
                ' For each list, reset our variables
                retID = ""
                listVersion = 0
 
                ' Iterate through the attributes
                For Each oAttr In ListChildNodes.Attributes
                    Debug.Print "Name= " & oAttr.Name
                    Debug.Print "Value= " & oAttr.nodeTypedValue
 
                    'Look for the ID attribute
                    If oAttr.Name = "ID" Then
                        ' FOund it, so remember it in case this is our list
                        retID = oAttr.nodeTypedValue
                    End If
 
                    ' Look for the "Title" attribute
                    If oAttr.Name = "Title" Then
                        If oAttr.nodeTypedValue = sListName Then
                            ' Found our list, so remember it
                            ListName = oAttr.nodeTypedValue
                            bGotNode = True
                        End If
                    End If
 
                    ' Look for the "Version" attribute
                    If oAttr.Name = "Version" Then
                            ' Found our list, so remember it
                            listVersion = oAttr.nodeTypedValue
                    End If
 
                    ' Once we have found a title, ID and version, break
                    If retID <> "" And listVersion <> 0 And bGotNode = True Then
                       Exit For
                    End If
 
                Next oAttr
            End If
            ' Once we have found the data, break out of iteration
            If bGotNode = True Then
                Exit For
            End If
        Next
    Next
 
    If bGotNode = False Then
        Err.Raise 65000, "", "ERROR: Cannot find list named <" & sListName & "> in Sharepoint site.", "", ""
    End If
 
    sListName = ListName
    sListID = retID
    iListVersion = listVersion
 
    Exit Function
 
GetListCollection_OnError:
    MsgBox ("ERROR: Retrieving list collection from Sharepoint." + vbCrLf + _
           Err.Number + " - " + Err.Description)
End Function

Remember, you will have to have installed the Microsoft Web Services Toolkit before this will work, and add a web reference to your Sharepoint site to your VBA project. This has been tested with Office 2003 linking to Sharepoint 2007 and works :).

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>" + _
  "</root>"
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)
Else
    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: http://the-simple-programmer.blogspot.com/2010/04/vba-code-to-iterate-through-results-of.html
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.