Add Items in Sharepoint Office 365 List using Vba (Giving Error Access Denied)

krishnaoptif

Board Regular
Joined
Sep 17, 2010
Messages
140
I am using below VBA code to add items in SharePoint 2010 and it is working fine.. but now my I am trying to migrate from sharepoint 2010 (All lists) to Office 365 SharePoint... but the same VBA code is not working now for Office 365 sharepoint site. It was giving me some kind of Access denied issue then i also tried to add my user name and password after ( objXMLHTTP.Open "POST", SharePointUrl + "_vti_bin/Lists.asmx", False, "KrishnaK@mySite.onmicrosoft.com", "MyPassword@123") code line but still giving me the access denied issue. Please suggest me that how to do this..



Code:
Const SharePointUrl = "https://mySite.sharepoint.com/"
Const ListNameOrGuid = "{F7191B56-3CC7-42E1-BA59-42291D4E6838}" 
Sub Add_ItemInSharePointList(c As MyCls)
    '=======================================================================
    '   Variable ini
    '=======================================================================
    Dim objXMLHTTP As MSXML2.XMLHTTP
    Dim strBatchXml, strBatchXml2 As String
    Dim strSoapBody As String
    '=======================================================================
    '   Set obj
    '=======================================================================
    Set objXMLHTTP = New MSXML2.XMLHTTP
    '=======================================================================
    '   Add a new item
    '=======================================================================
    strBatchXml = "<Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name='Title'>" + c.C1 + "</Field><Field Name='CustomerName'>" + c.C2 + "</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>" & ListNameOrGuid _
     & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"
     objXMLHTTP.send strSoapBody
    If objXMLHTTP.Status = 200 Then
       'Do something with response
       MsgBox "Records is added"
    End If
    Set objXMLHTTP = Nothing
End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

krishnaoptif

Board Regular
Joined
Sep 17, 2010
Messages
140
I thing I have noticed that :

Note: I was running this code on http and it was working fine but office 365 sharepoint is on https ... May be that is why this is not working....

Do I need to change any code line for https ?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,574
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top