SharePoint Lists, GetListItems, XML, and VBA - I just want to crossreference!

Question

I have a SharePoint list with, among other things, two columns that I care about; in Excel-ese, I want to match value X in column 1, and return the corresponding value in column 2. I'm able to use a variant of code at http://guruj.net/node/63 to retrieve the information (I think), so I think my problem focuses on navigating XML in VBA without downloaded DLLs (I do have a pile of MSXML?.DLLs, 2, 2.6, 3, 4, 5, 6).

The closest MSDN articles I find reference .NET (I'm stuck in the VBA/VB6? that comes with Office 2003) or a downloadable DLL.

Nat's response below outlines most of what I've found or need but like what I've found it's in the wrong language and part of my problem is finding search terms. I've mucked together some of a translation, for example, XMLDocument seems to be DOMDocument, but the XML parts are IXMLDOMNode or similar.

The major problem I'm running into at the moment is that I'm getting type mismatch on the SOAP call to perform the query, or object does not support that method when I try recasting the players (listQuery, listViewFields, listQueryOptions). I've left various parts as variant, and then cast them as the type returned (since I have no SP specific library "referenced", I'm doing this a little blind) and gotten mismatches.

It almost seems like it'd be worlds easier to screen scrape for the text.

Answer 1

Woah, you are so far down the rabbit hole...

Okay, the code you are looking at is getting you a view on the SharePoint list, which maybe not the best place to start, but the fact that it is all being done in VBA makes it really hard. I have .NET C# code to query a list and retrieve the items that have a particular value. The VBA conversion I am not able to do.

public static string GetPageId(string listName, string webPath, string pageTitle)
    {
        string pageId = "";
        IntranetLists.Lists lists = new IntranetLists.Lists();
        lists.UseDefaultCredentials = true;
        lists.Url = webPath + "/_vti_bin/lists.asmx";
        XmlDocument doc = new XmlDocument();
        doc.LoadXml("<Document><Query><Where><Contains><FieldRef Name=\"Title\" /><Value Type=\"Text\">" + pageTitle + "</Value></Contains></Where></Query><ViewFields /><QueryOptions /></Document>");
        XmlNode listQuery = doc.SelectSingleNode("//Query");
        XmlNode listViewFields = doc.SelectSingleNode("//ViewFields");
        XmlNode listQueryOptions = doc.SelectSingleNode("//QueryOptions");

        Guid g = GetWebID(webPath);

        XmlNode items = lists.GetListItems(listName, string.Empty, listQuery, listViewFields, string.Empty, listQueryOptions, g.ToString());
        foreach (XmlNode listItem in SPCollection.XpathQuery(items, "//sp:listitems/rs:data/z:row"))
        {
            XmlAttribute id = listItem.Attributes["ows_Id"];
            if (id != null)
            {
                pageId = id.Value;                    
            }

        }
        return pageId;            
    }

The IntranetLists is a .net web reference to the lists.asmx file.

You will have to research how to use the lists.asmx web service from within VBA, you will then have to call GetListItems with a query that represents the column value you want to look up.

<Where><Contains><FieldRef Name="Title" /><Value Type="Text">MyValue</Value></Contains></Where>

The syntax for that query is CAML

then you are going to have to parse the xml that comes back to find the item and the item field with the value you require. Any xpath query is going to have to have the correct namespaces added, e.g.

  public static XmlNodeList XpathQuery(XmlNode xmlToQuery, string xPathQuery)
    {
        XmlDocument doc = new XmlDocument();
        doc.LoadXml(xmlToQuery.OuterXml);
        XmlNamespaceManager mg = new XmlNamespaceManager(doc.NameTable);
        mg.AddNamespace("sp", "http://schemas.microsoft.com/sharepoint/soap/");
        mg.AddNamespace("z", "#RowsetSchema");                                   
        mg.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
        mg.AddNamespace("y", "http://schemas.microsoft.com/sharepoint/soap/ois");
        mg.AddNamespace("w", "http://schemas.microsoft.com/WebPart/v2");
        mg.AddNamespace("d", "http://schemas.microsoft.com/sharepoint/soap/directory");
        return doc.SelectNodes(xPathQuery, mg);
    }

However I am not sure you would even have access to something that would parse xml within your VBA setup, so you may have to download some extra VBA tool for doing that - may even be blocked for you.

Hope this helps a little.

Answer 2

Nat? Brilliant. Where have you been all my life?