使用 VBA 从 Excel 表中导入 Sharepoint 2010 列表数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20116896/
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
Import Sharepoint 2010 list data from Excel table using VBA
提问by user3016795
i have learned how to gather data from a sharepoint list into Excel using VBA simple macro only.
我已经学会了如何仅使用 VBA 简单宏将共享点列表中的数据收集到 Excel 中。
Now i would like to do the other way around - update some list in my Excel file, and send them back to sharepoint to update the list, using VBA only.
现在我想反过来做 - 更新我的 Excel 文件中的一些列表，然后将它们发送回 sharepoint 以更新列表，仅使用 VBA。
is that possible, and if yes - how?
这是可能的，如果是 - 如何？
Thanks!
谢谢！
回答by ErinsMatthew
Yes. You can use the XMLHttpRequestobject provided by Microsoft's XML SDK, as well as the UpdateListItems web service provided by SharePoint to update one or more items. Add a reference to "Microsoft XML, v6.0" in the Tools -> References menu in your Visual Basic Editor, and then use something like the code below.
是的。您可以使用XMLHttpRequestMicrosoft 的 XML SDK 提供的对象以及 SharePoint 提供的 UpdateListItems Web 服务来更新一项或多项。在 Visual Basic 编辑器的工具 -> 引用菜单中添加对“Microsoft XML，v6.0”的引用，然后使用类似下面的代码。
Dim objXMLHTTP As MSXML2.XMLHTTP
Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String
Set objXMLHTTP = New MSXML2.XMLHTTP
strListNameOrGuid = "My List Name or GUID"
' Delete item with internal ID of "1"
strBatchXml = "<Batch OnError='Continue'><Method ID='1' Cmd='Delete'><Field Name='ID'>1</Field></Method></Batch>"
objXMLHTTP.Open "POST", "http://myserver/mysite/_vti_bin/Lists.asmx", False
objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"
strSoapBody = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " _
& "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
& "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems " _
& "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & strListNameOrGuid _
& "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"
objXMLHTTP.send strSoapBody
If objXMLHTTP.Status = 200 Then
' Do something with response
End If
Set objXMLHTTP = Nothing
You can read more about the syntax of the UpdateListItemsand how the batch XML should be structured by going here.
回答by Rogier Wijsman
Not the answer to your question but maybe interesting enough. You can pull data from a Sharepoint list with PowerQuery,
不是你问题的答案，但也许足够有趣。您可以使用 PowerQuery 从 Sharepoint 列表中提取数据，
