使用VBA宏将Excel文件数据中的值插入SharePoint列表 [英] Insert values from Excel file data into SharePoint list using VBA Macro

问题描述

类似于以下问题 

https://social.msdn.microsoft.com/Forums/exchange/ en-US / 2a8b3a99-29d4-475e-bcf9-33cb12c3f34b / vba-to-insert-record-to-an-existing-sharepoint-list？forum = sharepointdevelopmentprevious

将项目插入SPList使用VBA Macro，我很难找到一个解决方案来更新/插入一些包含查询列，人员选择器控件和计算列的SPList

的1600条记录。

我的桌面/文件系统中有excel，我想读取这些excel值并插入/更新批量数据或使用VBA宏一步一步。

有可能吗？

 
  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   
 
  设置  objXMLHTTP   =    新  MSXML2  。  XMLHTTP 
 
 strListNameOrGuid   =     ListName   
 
 
  '添加新项'  
 strBatchXml   =    "< Batch OnError ='Continue'>< Method ID ='3'Cmd ='New'>< Field Name ='ID'> New< / Field>< ;字段名称="     +     FieldNameVar     +    ">"     +     ValueVar     +    "< / Field>< / Method>< / Batch>"   
 
 
 objXMLHTTP  。 打开   " 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  。 发送strSoapBody 
 
  如果  objXMLHTTP  。 状态    =     200    然后  
  '做一些回复
结束如果
 
设置objXMLHTTP =没有
 
结束子 
 
 上述内容不适用于插入/更新单个或多个项目。 
 

解决方案

不熟悉Excel VBA宏，但是想要将Excel数据加载到SharePoint列表行按行，这是一个非常好的示例代码片段将实现此要求：

将Excel数据上传到SharePoint自定义列表

关于Excel VBA Macro相关问题，我建议在此论坛中创建线程重点关注Excel开发问题：

https：// social。 msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

谢谢

最佳此致

Similar to the question below 

https://social.msdn.microsoft.com/Forums/exchange/en-US/2a8b3a99-29d4-475e-bcf9-33cb12c3f34b/vba-to-insert-record-to-an-existing-sharepoint-list?forum=sharepointdevelopmentprevious

inserting an item into SPList using VBA Macro, am struggling to find a solution to update/insert some 1600 records into an SPList

that has lookup columns, people picker controls, calculated columns.

Am having that excel in my desktop/ file system, i wanna read these excel values and insert/update bulk data or step by step using VBA Macro .

is it possible?

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
The above didn't work for inserting/updating single or multiple items.

解决方案

Hi,

Not very familiar with Excel VBA Macro, but if want to load Excel data into SharePoint list row by row, here is a very nice example code snippet will achieve this requirement:

Upload Excel data into SharePoint Custom List

And about Excel VBA Macro related question, I would suggest to create thread in this forum which is focued on Excel Development issue:

https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

Thanks

Best Regards

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