使用Excel (MSXML2.XMLHTTP对象)更新SharePoint列表
使用Excel (MSXML2.XMLHTTP对象)更新SharePoint列表
提问于 2017-03-05 14:13:50
通用信息.：我需要从SharePoint更新(添加/编辑)一个SharePoint列表。我可以用一个ListObject来完成它，但是这不是我们要走向的方向。
在阅读了谷歌的所有可能性之后，我想到了(也许我错了)使用MSXML2.XMLHTTP对象更新SharePoint列表的想法。
当前问题：下面的代码一直运行到最后，得到objXMLHTTP.Status = 200，但是SharePoint列表没有更新。
码
Option Explicit
Const SharepointUrl As String = "http://share.corning.com/sites/ipp/PMOSandbox/"
Const ListName As String = "{60CE6622-D25B-447A-BFBF-8F3DD5B9FCF0}"
Const VIEWNAME As String = "{91ADBAE5-479F-4C80-A5FF-8EDA7A233B82}"
Sub Add_Item()
Dim objXMLHTTP As MSXML2.XMLHTTP
Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String
Dim ValueVar As String, FieldNameVar As String
Set objXMLHTTP = New MSXML2.XMLHTTP
FieldNameVar = "IPP #"
ValueVar = "Shai"
'Add New Item'
strBatchXml = "<Batch OnError='Continue'><Method ID='1' Cmd='New'><Field Name='" + FieldNameVar + "'>1004</Field>" + _
"<Field Name='Title'>Uploaded from VBA</Field>" + _
"<Field Name='Next KD Status'>" + ValueVar + "</Field>" + _
"</Method></Batch>"
objXMLHTTP.Open "POST", SharepointUrl + "_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>" & ListName _
& "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"
objXMLHTTP.send strSoapBody
Do
' wait for response
Loop Until objXMLHTTP.Status = 200
Set objXMLHTTP = Nothing
MsgBox "Finished Running !"
End Sub回答 1
Stack Overflow用户
回答已采纳
发布于 2017-03-05 17:40:19
以下是如何使用ADO实现此操作的基本大纲。
Option Explicit
Public Sub Update()
On Error GoTo ErrHand
'Create the connection object with ADO
Dim conn As Object: Set conn = CreateObject("ADODB.Connection")
'Write your update statement
Dim sql As String
sql = "Update MyTable set Field1= 'A', Field2='B' Where MyID=1234"
'Open the connection and submit the update
'In my experience, credentials should be requested by the server -
'by way of a windows pop-up
With conn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
"DATABASE=MySharePointURL;" & _
"LIST={MyListGUID};"
.Open
.Execute sql
End With
CleanExit:
If conn.State = 1 Then conn.Close: Set conn = Nothing
Exit Sub
ErrHand:
Debug.Print Err.Number, Err.Description
GoTo CleanExit
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42609419
复制相关文章
相似问题
腾讯云开发者
