使用Excel-VBA（MSXML2.XMLHTTP对象）更新SharePoint列表 [英] Use Excel-VBA (MSXML2.XMLHTTP object) to update SharePoint List

问题描述

常规信息：我需要从Excel更新（添加/编辑）SharePoint列表。我能够使用 ListObject 来实现，但这不是我们导向的方向。

General Info.: I need to update (add/edit) a SharePoint list from Excel. I was able to do it with a ListObject, however this is not the direction we are leading to.

之后阅读所有Google的可能性，我想出（可能我错了）想要使用 MSXML2.XMLHTTP 对象来更新SharePoint列表。

After reading through all Google possibilities, I came up (maybe I'm wrong) with the idea to use MSXML2.XMLHTTP object to update the SharePoint List.

当前问题：我的代码如下，直到结束，我得到 objXMLHTTP.Status = 200 ，但SharePoint列表未更新。

Current Problem: My code below runs through untill the end, I get objXMLHTTP.Status = 200, but the SharePoint List is not updated.

代码

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

推荐答案

是如何使用ADO的基本概要。

Here is a basic outline of how to do this with 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

这篇关于使用Excel-VBA（MSXML2.XMLHTTP对象）更新SharePoint列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！