使用 VBA 从 Excel 表中导入 Sharepoint 2010 列表数据 [英] Import Sharepoint 2010 list data from Excel table using VBA

问题描述

我已经学会了如何仅使用 VBA 简单宏将共享点列表中的数据收集到 Excel 中.

i have learned how to gather data from a sharepoint list into Excel using VBA simple macro only.

现在我想反过来做 - 更新我的 Excel 文件中的一些列表，然后将它们发送回 sharepoint 以更新列表，仅使用 VBA.

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.

这可能吗，如果是，怎么办?

is that possible, and if yes - how?

谢谢！

推荐答案

是的.您可以使用 XMLHttpRequest 对象，以及 SharePoint 提供的用于更新一个或多个项目的 UpdateListItems Web 服务.在 Visual Basic 编辑器的工具 -> 引用菜单中添加对Microsoft XML，v6.0"的引用，然后使用类似下面的代码.

Yes. You can use the XMLHttpRequest object 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.

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 UpdateListItems and how the batch XML should be structured by going here.

这篇关于使用 VBA 从 Excel 表中导入 Sharepoint 2010 列表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！