Sharepoint 2007 Batch Update example using web services and CAML

Here is an example of running a batch update process on Sharepoint 2007 using web services. It was written in C# in Visual Studio 2008.

In this example, I take the value of a text field "Date_x0020_of_x0020_Birth", turn this into a DateTime value, and then use this to update the the value of a DateTime field "Date_x0020_of_x0020_Birth0". Obviously this section of the code will not be particularly generalisable, however I have left in all the code in order to show you the principles involved, and hopefully you will find it useful in building your own batch routines.

In order to get it running, you must create a reference to the Sharepoint Lists web service and call it "ListsService". The SDK shows you how to do this, and is available here: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=6D94E307-67D9-41AC-B2D6-0074D6286FA9&displaylang=en

The GetListItems() method of the Lists web service is used to query the data, and the UpdateListItems() method is used to update items.

I used an XPathNavigator object to filter only rows that contain data. You could just loop through the nodes if you prefer.

CAML is used to query the Sharepoint list and look for items where the "Date_x0020_of_x0020_Birth" is null. Tailor this to your own needs.
For CAML reference, look at the MSDN article here: http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems%28v=office.12%29.aspx

The CAML update command uses owshiddenversion, and so is useful in situations where versioning is enabled. Lists without versioning do not need this field.

If the UpdateListItems command returns an error, in my experience this is usually caused by using an incorrect internal name for a field.  You may need to add the URI prefix "urn:schemas-microsoft-com:office:office#" to the field name (e.g. change line 42 to something like:  "<Field Name=\"urn:schemas-microsoft-com:office:office#{4}\">{5}</Field>").

Full code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Net;

namespace BatchUpdateDOB
{
    class Program
    {
        static string LogFile = "D:\\BatchUpdateLog.txt";  // Log file location

        static void Main(string[] args)
        {
            try
            {
                StatusMessage("Writing log file to: " + LogFile);
           
                // Set up the variables to be used.
                StringBuilder methodBuilder = new StringBuilder();
                string batch = string.Empty;
                DateTime currentDate = DateTime.Now;
                string formattedDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentDate);
                string fieldToReference = "Date_x0020_of_x0020_Birth";
                string fieldToUpdate = "Date_x0020_of_x0020_Birth0";

                string batchFormat = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                  "<ows:Batch OnError=\"Return\">{0}</ows:Batch>";

                
                // CAML update command for web service
                string methodFormat = "<Method ID=\"1\" Cmd=\"Update\">" +
                 "<Field Name=\"ID\">{1}</Field>" +
                 "<Field Name=\"FileRef\">{2}</Field>" +
                 "<Field Name=\"owshiddenversion\">{3}</Field>" +
                 "<Field Name=\"{4}\">{5}</Field>" +
                 "</Method>";

                //UPDATE THESE TWO LINES TO SITE SPECIFIC VALUES
                string siteName = "http://<your site root uri here>";
                string listName = "<your list name here>";

                //string viewName = "";  // No need for viewname
                int rowLimit = 10;       //The number of items returned per batch (not used)

                StatusMessage("Processing " + siteName + ", List: " + listName + "...");

                //Web service setup
                //ListService should be the name of your web service reference
                ListService.Lists listService = new ListService.Lists();
                listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
                listService.Url = siteName + "/_vti_bin/Lists.asmx";
                

                //Build CAML query to search for records
                XmlDocument camlDocument = new XmlDocument();
                XmlNode queryNode = camlDocument.CreateElement("Query");
                //queryNode.InnerXml =  "<Where><IsNull><FieldRef Name='Date_x0020_of_x0020_Birth'/></IsNull></Where>";
                //queryNode.InnerXml = "<OrderBy><FieldRef Name='Country' /></OrderBy>";

                XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");
                viewFieldsNode.InnerXml = "<FieldRef Name='Title' />" +
                  "<FieldRef Name='owshiddenversion' />" +
                  "<FieldRef Name='" + fieldToReference + "' />" +
                  "<FieldRef Name='" + fieldToUpdate + "' />" +
                  "<FieldRef Name='FileRef' />";

                XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
                queryOptionsNode.InnerXml = "<ViewAttributes Scope=\"Recursive\" /><IncludeMandatoryColumns>True</IncludeMandatoryColumns>";

                // Get the GUID of the list
                XmlNode ndListView = listService.GetListAndView(listName, "");
                string listGuid = ndListView.ChildNodes[0].Attributes["Name"].Value;
                string listVersion = ndListView.ChildNodes[0].Attributes["Version"].Value;

                // Run the CAML query
                XmlNode resultNode = listService.GetListItems(listGuid, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);
                // If you want to put a rowlimit on, then use this command:
                //XmlNode resultNode = listService.GetListItems(listGUID, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);

                long totalRecords = 0;
               
                //string itemDOB;
                string dob;
                DateTime dtDOB;
                string owsHiddenVersion;  
                string fullFileRef;
                string owsFileRef;
               

                // Create xpath navigator   
                XPathNavigator navigator = resultNode.CreateNavigator();
                // Set up namespace manager for XPath   
                XmlNamespaceManager ns = new XmlNamespaceManager(navigator.NameTable);
                ns.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
                ns.AddNamespace("z", "#RowSetSchema");

                // Select only the z:row nodes
                XmlNodeList nodes = resultNode.SelectNodes("//*[name()='z:row'] ", ns);

                // Iterate through the returned nodes
                foreach (XmlNode node in nodes)
                {
                    string itemID = node.Attributes["ows_ID"].InnerText;
                    try
                    {
                        dob = node.Attributes["ows_" + fieldToReference].InnerText;
                        owsHiddenVersion = node.Attributes["ows_" + "owshiddenversion"].InnerText;
                        owsFileRef = node.Attributes["ows_" + "FileRef"].InnerText;
                        // For some reason my data is returned in the format: 2#/listname/path, 
                        // where 2 seems to be an id or version number, and listname/path points to the file in relation to the root site.
                        // The following strips that out and gets the full path (required for the CAML update)
                        fullFileRef = siteName + "/" + owsFileRef.Remove(0, owsFileRef.IndexOf("#") + 1);
                    }
                    catch(Exception ex)
                    {
                        // Ignore this row
                        //StatusMessage("ERROR: Cannot process: " + node.InnerXml.ToString() + ": " + ex.Message); // If you want to see why, uncomment
                        continue;
                    }

                    if (String.IsNullOrEmpty(dob)) continue;  // Ignore list items with no value

                    dtDOB = Convert.ToDateTime("01/01/1900"); // Set default value for dates before 1900

                    // Convert date text to DateTime
                    try
                    {
                        dtDOB = Convert.ToDateTime(dob);
                    }
                    catch (Exception ex)
                    {
                        StatusMessage("Cannot process: " + siteName + "/" + fullFileRef + " DOB: " + dob + ".  Setting to 01/01/1900. " + ex.Message);
                    }

                    MessageToFile("Processing: " + fullFileRef + ", DOB = " + dob);

                    methodBuilder.AppendFormat(methodFormat, listGuid, itemID, fullFileRef, owsHiddenVersion, fieldToUpdate, SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDOB));

                    // Put the pieces together.
                    batch = string.Format(batchFormat, methodBuilder.ToString());
                    //MessageToFile(batch);

                    //Build CAML command to update using Web service
                    XmlDocument xmlDoc = new XmlDocument();
                    XmlElement elBatch = xmlDoc.CreateElement("Batch");
                    elBatch.SetAttribute("OnError", "Continue");
                    elBatch.SetAttribute("ListVersion", listVersion.ToString());
                    elBatch.SetAttribute("ViewName", "");
                    elBatch.InnerXml = methodBuilder.ToString();

                    // This line runs the CAML update command
                    XmlNode updatesResponse = listService.UpdateListItems(listGuid, elBatch);
                    if (updatesResponse.FirstChild.FirstChild.InnerText != "0x00000000") throw new Exception("Could not update properties.\n\n" + updatesResponse.InnerText + "\n" + updatesResponse.FirstChild.FirstChild.InnerText);
                    StatusMessage(fullFileRef + " processed...");

                    totalRecords++;
                }
                StatusMessage("Completed - " + totalRecords + " processed.");
            }
            catch (Exception exp)
            {
                StatusMessage("ERROR: " + exp.Message);
                
            }
            
        }

        
        /// <summary>
        /// Display a  message
        /// </summary>
        /// <param name="myMessage"></param>
        static void StatusMessage(string myMessage)
        {
            Console.WriteLine(myMessage.ToString()); 
            MessageToFile(myMessage);
        }

        /// <summary>
        /// Write message to the log file
        /// </summary>
        /// <param name="myMessage"></param>
        static void MessageToFile(string myMessage)
        {
            using (StreamWriter sw = new StreamWriter(LogFile, true))
            {
                DateTime now = DateTime.Now;
                sw.WriteLine(now.ToString() + ": " + myMessage.ToString());
            }
        }
    }
}

Binding a CheckBoxList object to an ordered Sharepoint list of reference values in a web part (c#)

It's common when creating database applications to use reference tables to provide lookup values.  These tables ofter contain a flag that is used to mark a record as no longer available, and an order column that provides a method of putting the values in an order of importantance, perhaps when using them in a drop down list or list of checkboxes.  Sharepoint is NOT a database, however there is no reason why good practice such as this cannot be applied to Sharepoint's list model.

In the following example I will show you how to set up a reference list and then use the Sharepoint object model to programmatically access this list and bind it to an control (in this case a Sharepoint CheckBoxList control).  Once you understand this, you can use the same method to connect to any reference list and bind it to other objects and controls, such as drop down lists. This example has been developed for Sharepoint 2007, but should also work for Sharepoint 2010.








Step 1:  Set up a reference list
Create a new list on your Sharepoint site with teh following structure: 

• ID - integer (this is a default field) - this will be used as the unique key (lookup value) used to link this reference filed to other lists
• Title - string (this is a default field) - this is used to hold the description of the reference field, and will be the value shown in lookups
• SortOrder - integer, default 1000 - this can be used to sort the list values in lookups and programmatically using CAML
• Enabled - Yes/No field, default Yes - setting to NO will enable you to filter out these values in views and in programmatic lookups in CAML

You can now enter you reference values into the list, for example here is a list of types of medical record:

Step 2:  Add method to webpart that use CAML to read and order the list
Now for the code that reads the Sharepoint list in a structured manner so we can use it in web parts.  First, add the following method to your web part:

/// <summary>
/// Returns a list collection, ordered using a field in the list called "SortOrder"
/// </summary>
/// <param name="listName">The literal string name of the list to query</param>
/// <returns>SPListItemCollection</returns>
private SPListItemCollection ReadOrderedList(string listName)
{
    try
    {
        using (SPWeb configWeb = SPContext.Current.Site.OpenWeb())
        {
            //Get the list
            SPList lookupList = configWeb.Lists[listName];
            // Use CAML to order the list
            SPQuery query = new SPQuery();
            // This CAML where clause searches for only items where the Enabled field is set to "Yes"
            query.Query = "<Where><Eq><FieldRef Name=\"Enabled\" /><Value Type=\"Integer\">1</Value></Eq></Where> ";
            // This CAML order by claus uses an integer field of "SortOrder"
            query.Query += "<OrderBy><FieldRef Name=\"SortOrder\" /></OrderBy>";
            SPListItemCollection lookupItems = lookupList.GetItems(query);
            return lookupItems;
        }
    }
    catch (Exception ex)
    {
        ErrorTrap("ReadOrderedList: " + ex.Message + Environment.NewLine + ex.StackTrace.ToString());
        throw;
    }
} 

This method will return the content of the list in a SPListItemCollection object. CAML is use to order the list by the "SortOrder" column, and to ignore values that do not have the boolean field "Enabled" set to true.

Step 3:  Bind the list to a control
At the class level of your webpart, create the control that you wish to bind to (in this case a CheckBoxList):

CheckBoxList myCheckList = new CheckBoxList();

To bind this control to your Sharpoint list, put this code in the CreateChildControls() method. Simply replace "My List" with the real name of your list:

// Set up the Record Section checkboxes list
    SPListItemCollection dt = ReadOrderedList("My List");
    myCheckList.DataSource = dt;
    myCheckList.DataTextField = "Title";
    myCheckList.DataValueField = "ID";
    myCheckList.DataBind();

That's it! You now have a Sharepoint object bound to your Sharepoint list of reference values.  When rendered, the output will look something like this:

Yyou can use the following method to render the CheckBoxList to the web part.  This example puts the CheckBoxList in a DIV that fixes the maximum size of the list with a scrollbar.

/// <summary>
/// render a list of checkboxes  to the page
/// </summary>
void RenderCheckBoxes()
{
    try
    {
        // Set up list of checkboxes
        myCheckList.EnableViewState = true;
        myCheckList.CellSpacing = 0;
        myCheckList.CellPadding = 0;

        for (int i = 0; i < myCheckList.Items.Count; i++)
        {
            ListItem li = new ListItem(myCheckList.Items[i].Text, myCheckList.Items[i].Value);
           
            // Set all boxes to selected if not a postback
            if (!Page.IsPostBack)
            {
                myCheckList.Items[i].Selected = true;
            }

        }
        // Give the checklist an ID (can use this in javascript etc)
        myCheckList.ID = "RecordSections";

        //Add controls to web part
        Controls.Add(new LiteralControl(@"<TABLE border='0' bordercolor='red' cellpadding='0' cellspacing='0' valign='top'>"));
        Controls.Add(new LiteralControl("<TR>"));
        Controls.Add(new LiteralControl("<TD>"));
        Controls.Add(new LiteralControl(@"<div class=""ms-frombody""><b>Record Sections</b></div>"));
        Controls.Add(new LiteralControl("</TD>"));
        Controls.Add(new LiteralControl("</TR>"));
        Controls.Add(new LiteralControl("<TR>"));

        Controls.Add(new LiteralControl("<TD>\r\n"));
        Controls.Add(new LiteralControl(@"<input type=""hidden"" id=""ScrollPos"" runat=""server""/>"));

        Controls.Add(new LiteralControl(@"<div id='RecordSectionScroll' style=""OVERFLOW: auto; WIDTH: 247px;  TOP: 0px; HEIGHT: 300px"" onscroll=""javascript:document.getElementById('ScrollPos').value = this.scrollTop""> "));

        Controls.Add(myCheckList);
        Controls.Add(new LiteralControl("</div></TD>"));
        Controls.Add(new LiteralControl("</TR>"));     
        Controls.Add(new LiteralControl("</TABLE>"));
    }
    catch (Exception ex)
    {
        ErrorTrap("RenderCheckBoxes" + ex.Message + Environment.NewLine + ex.StackTrace.ToString());
    }
}

Note that I am using literal controls to render the check boxes in a HTML table. This is pretty horrible and I don't recommend it, but it does work.

Here is the ErrorTrap method. Note that this is just a simple error handling function and is not required to render a checklist bound to a Sharepoint list, but since it appears in the examples I though I would show you what I do.

/// <summary>
/// Give an error message
/// </summary>
/// <param name="myMessage"></param>
void ErrorTrap(string myMessage)
{
    Controls.Add(new LiteralControl("<strong>ERROR: " + myMessage.ToString() + "<BR/></strong>"));
    ErrorToFile(myMessage);          
}

/// <summary>
/// Write errors to the log file
/// </summary>
/// <param name="myMessage"></param>
void ErrorToFile(string myMessage)
{
    using (StreamWriter sw = new StreamWriter("D:\\SearchErrorLog.txt"))
    {
        DateTime now = new DateTime();
        sw.Write(now.ToString() + ": " + myMessage.ToString());
    }
}

Render Content Type fields as a form in Sharepoint Web Part using c#

In this blog entry I will give you some example code that will render a simple form in a Sharepoint 2007 web part. The form fields are taken from a pre-defined Sharepoint Content Type. I struggled to find any examples of this on the net, despite it seeming to me to be a fairly ordinary thing to want to do (after all, if you go to all the trouble of defined your own Content Types to organise your data, surely you would want to use them in your own web parts?).

In order to develop this, I used C# / Visual Studio 2008 and Sharpoint 2007. This should also work in Visual Studio 2005, although you will have to install the web part manually - Visual Studio 2008 has far tighter links to Sharepoint, meaning debugging and deploying is much easier. It should also work in VS2010 / Sharepoint 2010.

The following method will return a SPContentType object, you only need to pass it a list name and content type name.  The SPContentType object can be used later to iterate through its fields in oder to render controls based on the content type's fields.

/// <summary>
/// Returns the content type object
/// </summary>
/// <param name="listName"></param>
/// <param name="contentTypeName"></param>
/// <returns></returns>
SPContentType ReadContentType(string listName, string contentTypeName)
{

    //get the web
    this._web = Microsoft.SharePoint.SPContext.Current.Web;

    SPList dirList = this._web.Lists[listName];
    SPContentType contentType = dirList.ContentTypes[contentTypeName];

    //You could simply iterate here like this if you wish, like this:
    //foreach (SPField field in _contentType.Fields)
    //{
    //    string dispName = field.Title;
    //    SPFieldType type = field.Type;
    //    object defaultValue = field.DefaultValue;

    //}

    return contentType;
}

The following code overrides the "CreateChildControls" Sharepoint method to create our own controls based on the content type.  In this example, only text fields and date-times are rendered, but hopefully it straight forward enough for you can see how you could extend this. Simply change: 

"Your_List_Name" and "Your_Content_Type_Name" 

to your actual values from your Sharepoint site.
I use the "LiteralControl" method to render raw HTML on the page to format the form, which feels really nasty but is efective.

protected override void CreateChildControls()
{
    //Controls.Clear();
    //base.CreateChildControls();

    // Read in the content type for the target list
    SPContentType contentType = ReadContentType("Your_List_Name", "Your_Content_Type_Name");
    
    Controls.Add(new LiteralControl("<TABLE>"));

    foreach (SPField field in contentType.Fields)
    {
        // Ignore hidden or readonly fields
        if (field.Hidden) continue;
        if (field.ReadOnlyField) continue;     

        if (field.Type == SPFieldType.Text)
        {
            string dispName = field.Title;
            string staticName = field.StaticName;
            SPFieldType type = field.Type;
            object defaultValue = field.DefaultValue;

            Controls.Add(new LiteralControl("<TR>"));
            Controls.Add(new LiteralControl("<TD>"));
            Label lbl = new Label();
            lbl.Text = dispName;

            Controls.Add(lbl);

            Controls.Add(new LiteralControl("</TD>"));

            // Textbox
            TextBox tb = new TextBox();
            // I give my controls a unique ID so I can reference them later
            tb.ID = "xx_ctl_xx" + dispName;
            
            if(defaultValue != null)
                tb.Text = defaultValue.ToString();

            Controls.Add(new LiteralControl("<TD>"));
            Controls.Add(tb);
            Controls.Add(new LiteralControl("</TD>"));
            Controls.Add(new LiteralControl("</TR>"));

        }
        else if (field.Type == SPFieldType.DateTime)
        {
            
            string dispName = field.Title;
            string staticName = field.StaticName;
            SPFieldType type = field.Type;
            object defaultValue = field.DefaultValue;

            Label lbl = new Label();
            lbl.Text = dispName;

            Controls.Add(new LiteralControl("<TR>"));
            Controls.Add(new LiteralControl("<TD>"));
            Controls.Add(lbl);
            Controls.Add(new LiteralControl("</TD>"));

            // Datetime picker
            SPDatePickerControl dtp = new SPDatePickerControl();
            dtp.ID = "xx_ctl_xx" + staticName;
           
            if (defaultValue != null)
                dtp.SelectedDate = defaultValue.ToString();

            Controls.Add(new LiteralControl("<TD>"));
            Controls.Add(dtp);
            
            Controls.Add(new LiteralControl("</TD>"));
            Controls.Add(new LiteralControl("</TR>"));
        }       
    }
    Controls.Add(new LiteralControl("</TABLE>"));

    //In my code, I use the controls to perform a custom search.  The following code plugs in
    //the event handler to do this, based on the values the user puts into the controls rendered above.
    //This blog post will not go into the detail of this so I have commented the following lines out.
    //cmdSearch = new Button();
    //cmdSearch.Text = "Start Search";
    //cmdSearch.Click += new EventHandler(cmdSearch_Click);
    //this.Controls.Add(cmdSearch);

    //lblQueryResult = new Label();                     
    //this.Controls.Add(lblQueryResult);
    
    base.CreateChildControls();
}

Once deployed, you can add this web part to your site in the normal way using the web front end.  Depending on the fields in your content type, it should render something like this (web part highlighted in pink):

Colour code a RAG column in Sharepoint 2007 list view using Jquery

To see the best selling SMART televisions visit Yngoo!
I'm a big fan of Microsoft Sharepoint, even though it really annoys me sometimes. It's amazing that no matter what question a client asks of it, it never can quite answer the question straight out of the box. However, this is not a problem, because Sharepoint is virtually infinately customisable by a competent programmer with enough time to become emersed in the "Sharepoint Way" and no fear of learning new techniques.
An example of this came up for me recently when a client requested that a column on a list would be colour coded depending on the value of the data in that column. The data would be a RAG rating (i.e. a "choice" field containing a set of values that relate to red, amber or green), and therefore it would be nice to colour code the column in eiter red, amber or green to match the rating. Sharepoint does not give you any easy way of doing this unfortunately.

This is where Jquery comes in. This terrific library of javascript offers developers a new and consise way of dealing with the internet document object model (DOM). With it developers can quickly and easily scan the object model of the page returned in the browser, and change the page before it is rendered. There is far more to it than this of course, but this is the trick I used to colour code the columns.

To colour (color) code a coloumn, follow these steps:

1. Download the jquery.js library from teh link above, and upload it to your Sharepoint site in a convenient location (I use the Site Documents Collection in my top level publishing site, but anywhere will do)
2. Edit the Sharepoint page that the holds the list, and add a Content Editor Web Part (CEWP).
   
   
   
   
   
   
   
   
3. Edit the CEWP, choose the "edit source" option, and paste in something like the following code
   
   
   
   
   
   
   
   
   

<script type="text/javascript" src="http://<root site>/SiteCollectionDocuments/jquery-1.4.2.js"></script>

<script type="text/javascript">

$(document).ready(function(){
$(".ms-vb2:contains('1: No Assurance')").each(function(){
$(this).css("background-color", "#FF0000");
//$(this).css("filter", "alpha(opacity=50)"); 
});

$(".ms-vb2:contains('2: Limited Assurance')").each(function(){
$(this).css("background-color", "#FF9900");
});

$(".ms-vb2:contains('3: Significant Assurance')").each(function(){
$(this).css("background-color", "#FFFF00");
});

$(".ms-vb2:contains('4: Full Assurance')").each(function(){
$(this).css("background-color", "#00CC00");
});

});
<script>

Note that in this example, the Jquery library is located at: http://<root site>/SiteCollectionDocuments/jquery-1.4.2.js. Change this to the URL of your own Jquery library.

In my example, the RAG rating column can contain the following values:

• 1: No Assurance  (RGB #FF0000)
• 2: Limited Assurance (RBG #FF9900)
• 3: Significant Assurance  (RGB #FFFF00)
• 4: Full Assurance (RGB #00CC00)

Now to explain what this is actually doing: "ms-vb2" is the standard Sharepoint site template CSS class that is used to identify columns in a list view web part.
What this Jquery script is effectively doing is looking for a DOM item with the class "ms-vb2", and in each case that it this
DOM item contains a specific piece of text ("1: No Assurance", "2: Limited Assurance" etc.), it modifies the CSS of this
particular section of the DOM to add a "background-color" CSS tag.

Of course your list will have different values, so remember to change the strings in bold to match your own list values!
The colour codes are standard RGB, so you may find this RGB table useful to decide your colours.


Your results will look something like this:

Best of luck!

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

Write lines to a RichTextBox control in different colours (VB .net example)

I create a lot of back end "processing" software, for example migration or upgrade tools that process documents as they find them on a server. As a standard, I tend to write their progress out to a text file, but I also like the program to show me what is happening as it processes files by writing the log file to the screen. This is particularly useful when debugging the application, but it's also far more satisfying to see something happening on the screen.

In this situation it is nice to write out warnings or non critical errors in different colours in order to attract the eye.

To achieve this, create a RichTextBox control on your main form (in my example it is called "fInfoBox"), and write to it using the code in the sub routine below.

Sub ShowLog(ByVal qFore as Drawing.Color, ByVal sMesg as string)
   Dim sDispMess As String
   sDispMess = Date.Now()& ": " & sMesg & vbCrLf 

   With fInfoBox 'This is the name of you RichTextBox control on your form
       .SelectionStart = Len(.Text)
       .SelectionBullet = True  ' This shows the line in bulleted form, comment out if not required

       .SelectionColor = qFore
       .SelectedText = sDispMess.ToString()
    End With

    fInfoBox.ScrollToCaret()  ' This scrols to the last line of the text box
End Sub

To display a message with a font colour of blue, call the routine like this:

ShowLog(Drawing.Color.Blue, "This message will appear in a font color of blue")

For a red message, try this:

ShowLog(Drawing.Color.Red, "ERROR: This message will display in a font color of red!!")

This is a very simple example, but I hope it helps someone get started with this sort of thing and gives you some ideas.

Remember that if you use threading or the backgroundworker class to remove your time consuming processes from the UI (which you should, especially since this is so easy after .Net 2.0), then you will need to use a callback technique for your worker thread to be able to talk to your UI thread.

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.