使用VBA从Excel将数据添加到Sharepoint 2013列表 [英] Add data to Sharepoint 2013 list from Excel with VBA

问题描述

我一直试图使用VBA将数据从Excel添加到Sharepoint 2013列表中，但没有成功.我使用在互联网上找到的示例作为模板.

I have been attempting to add data to a Sharepoint 2013 list from Excel using VBA with no success. I am using as a template the example that is found all over the internet.

Sub Add_Item(ListName As String, SharepointUrl As String, ValueVar As String, FieldNameVar As String)

Dim objXMLHTTP As MSXML2.XMLHTTP

Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String

Set objXMLHTTP = New MSXML2.XMLHTTP

strListNameOrGuid = ListName

'Add New Item'
strBatchXml = "<Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + FieldNameVar + ">" + 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>" & 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

End Sub

由于我是XML和SOAP的新手，所以我决定向后工作，只是尝试先将一个项目添加到列表中.我没有从工作表中获取数据的问题.我没有将任何参数传递给sub，我只是分配值.

Since I am new to using XML and SOAP I decided to work backwards and just try to add an item to the list first. I don't have an issue with getting the data from the sheet. I don't pass any parameters into the sub, I just assign values.

我没有任何错误.但是，也没有数据写入该列表.我什至得到200的状态-只是表示确定". 我什至尝试从列表名称或URL中删除一些字符，以查看是否会产生错误，但是我仍然得到200的状态.

I don't get any errors. But no data gets written to the list, either. I even get a status of 200 - which simply means "OK". I even tried taking some characters out of the list name or the url to see if that would give an error, but I still get a status of 200.

如果我使用Msxml2.ServerXMLHTTP而不是Msxml2.XMLHTTP，则我的状态为401.有什么办法可以强制执行错误操作，从而使我看到需要更正才能使它正常工作?

If I use Msxml2.ServerXMLHTTP instead of Msxml2.XMLHTTP I get a status of 401. Is there any way I can force an error so I can see what I need to correct to get this to work?

这就是我正在使用的(希望我删除了所有敏感信息):

Here is what I am working with (hopefully I removed any sensitive information):

Sub Add_Item()

' Hard coding the parameters to start so just testing the output to SharePoint
Dim ListName As String
Dim SharepointUrl As String
Dim Branch_Cost_Center As String
Dim Field1NameVar
ListName = "Nightly Device Tracking Excel XML Test"
SharepointUrl = "https://{myurl}"
Field1NameVar = "Branch Cost Center"
Branch_Cost_Center = "919191"
' Remove the above when passing parameters

Dim objXMLHTTP As MSXML2.XMLHTTP

Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String

Set objXMLHTTP = New MSXML2.XMLHTTP

strListNameOrGuid = ListName


'Add New Item'
strBatchXml = "<Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + Field1NameVar + ">" + Branch_Cost_Center + "</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>" & strListNameOrGuid _
 & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

'for debugging
Debug.Print strSoapBody

objXMLHTTP.send strSoapBody

If objXMLHTTP.Status = 200 Then
' Do something with response
End If

Set objXMLHTTP = Nothing

End Sub

我尝试使用字段的内部名称和GUID代替列表名称，结果没有差异.

I have tried using the internal name for the field and the GUID instead of the list name with no difference in result.

我在objXMLHTTP上设置了一个watch变量，但不知道要查找什么-返回了很多信息.

I set a watch variable on the objXMLHTTP, but don't know what to look for - there is a lot of information returned.

推荐答案

您的字段名称必须在其周围加上单引号:

Your field names need to have single quotes around them:

Field1NameVar = "'Branch Cost Center'"

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