SharePoint 列表、GetListItems、XML 和 VBA - 我只想交叉引用！
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/445274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SharePoint Lists, GetListItems, XML, and VBA - I just want to crossreference!
提问by Serious
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 variantof code at http://guruj.net/node/63to 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).
我有一个 SharePoint 列表，其中包含我关心的两列；在 Excel-ese 中，我想匹配第 1 列中的值 X，并返回第 2 列中的相应值。我可以使用http://guruj.net/node/63上的代码变体来检索信息（我认为），所以我认为我的问题主要是在没有下载 DLL 的情况下在 VBA 中导航 XML（我确实有一堆 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.
我找到的最接近的 MSDN 文章参考了 .NET（我被困在 Office 2003 附带的 VBA/VB6？）或可下载的 DLL。
Nat's response below outlines most of what I've found or need butlike 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.
下面 Nat 的回复概述了我发现或需要的大部分内容，但就像我发现的语言错误一样，我的部分问题是查找搜索词。我把一些翻译混在一起，例如，XMLDocument 似乎是 DOMDocument，但 XML 部分是 IXMLDOMNode 或类似的。
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.
我目前遇到的主要问题是我在执行查询的 SOAP 调用中遇到类型不匹配，或者当我尝试重铸播放器（listQuery、listViewFields、listQueryOptions）时对象不支持该方法。我将各个部分作为变体保留，然后将它们转换为返回的类型（因为我没有“引用”特定于 SP 的库，所以我这样做有点盲目）并得到不匹配。
It almost seems like it'd be worlds easier to screen scrape for the text.
几乎似乎屏幕抓取文本会更容易。
回答by Nat
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.
好的，您正在查看的代码是让您查看 SharePoint 列表，这可能不是最好的起点，但所有这些都在 VBA 中完成的事实使它变得非常困难。我有 .NET C# 代码来查询列表并检索具有特定值的项目。我无法执行的 VBA 转换。
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.
IntranetLists 是对lists.asmx 文件的.net Web 引用。
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.
您必须研究如何在 VBA 中使用lists.asmx Web 服务，然后您必须使用代表要查找的列值的查询调用 GetListItems。
<Where><Contains><FieldRef Name="Title" /><Value Type="Text">MyValue</Value></Contains></Where>
The syntax for that query is CAML
该查询的语法是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.
那么您将不得不解析返回的 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);
}
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.
但是，我不确定您是否甚至可以访问可以在 VBA 设置中解析 xml 的内容，因此您可能需要下载一些额外的 VBA 工具来执行此操作 - 甚至可能会被阻止。
Hope this helps a little.
希望这有所帮助。
