Geek Answers Handbook

SharePoint Lists, GetListItems, XML and VBA - I Just Want To Cross!

I have a SharePoint list, among other things, two columns that concern me; in Excel-ese, I want to match the X value in column 1 and return the corresponding value in column 2. I can use the code option in http://guruj.net/node/63 to get the information (I think), so I think that my problem is focused on navigating XML in VBA without loaded DLLs (do I have a bunch of MSXML? .DLL, 2, 2,6, 3, 4, 5, 6).

In upcoming MSDN articles, I find a .NET link (I'm stuck in the VBA / VB6 that ships with Office 2003) or a downloadable DLL.

Nat's answer below describes most of what I found, or need , but like the fact that I found it in the wrong language, and part of my problem is finding search terms. I put together some of the translations, like XMLDocument, it seems, DOMDocument, but the XML parts are IXMLDOMNode or similar.

The main problem that I am facing now is that I get a type mismatch when calling SOAP to execute a request, or the object does not support this method when I try to remake players (listQuery, listViewFields, listQueryOptions). I left the various parts as an option, and then passed them as a return type (since I do not have a special SP library referenced, I do it a little blindly) and got inconsistencies.

It seems like it would be easier to put up with a screen for text.

+3
source share
1 answer

Voa, you are so far from the rabbit hole ...

Well, the code you are looking at gives you an idea of ​​the SharePoint list, which may not be the right place to start, but the fact that all this is done in VBA makes it very difficult. I have C # .NET code to query a list and get items that have a specific value. VBA conversion that I cannot 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;            
    }

IntranetLists is the .net web link for the lists.asmx file.

You will need to research how to use the lists.asmx web service from VBA, you will need to call GetListItems with a query representing the value of the column you want to find.

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

CAML

xml, , . xpath , .

  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);
    }

, -, xml VBA, , , VBA - .

, .

+4

More articles:


All Articles