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

问题描述

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

现在我想做其他的方法 - 更新我的Excel文件中的一些列表，并将它们发送到sharepoint更新列表，仅使用VBA。 / p>

是可能的，如果是 - 如何？

谢谢！

解决方案

是的。您可以使用 XMLHttpRequest 由Microsoft的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 
 
设置objXMLHTTP =新的MSXML2.XMLHTTP 
 
 strListNameOrGuid =我的列表名称或GUID
 
'删除内部ID为1的项
 strBatchXml =< ; Batch OnError ='Continue'>< Method ID ='1'Cmd ='Delete'>< Field Name ='ID'> 1< / Field>< / Method>< / Batch> 
 
 objXMLHTTP.OpenPOST，http：//myserver/mysite/_vti_bin/Lists.asmx，False 
 objXMLHTTP.setRequestHeaderContent-Type，text / xml; charset =UTF-8
 objXMLHTTP.setRequestHeaderSOAPAction，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><更新> 中&安培; strBatchXml& < /更新与GT;< / UpdateListItems>< /皂：身体与GT;< /皂：信封> 中
 
 objXMLHTTP.send strSoapBody 
 
如果objXMLHTTP.Status = 200然后
'做一些响应
结束如果
 
 Set objXMLHTTP = Nothing 
  

您可以阅读更多关于 UpdateListItems 以及如何通过这里。

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

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?

Thanks!

解决方案

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.

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