none
How to do CRUD operations using REST API from EXCEL VBA ? RRS feed

  • Question

  • Hello,

    I have excel file with VBA macro for different tabs and stored in document library.

    I require to write a code in EXCEL VBA using REST API to access the SharePoint list and perform the CRUD operations.

    Is there any reference link available to implement above scenario ?

    Thanks and Regards,


    Dipti Chhatrapati

    Friday, February 17, 2017 8:45 AM

Answers

  • Hi,

    You could check below sample code for your reference, hope it helps you.

    Sub RESTCall()
        Const sUrl As String = "http://sp:12001/_api/Web/Lists/getByTitle('Parent')/items(1)"
        Dim oRequest As WinHttp.WinHttpRequest
        Dim sResult As String
        
        Set oRequest = New WinHttp.WinHttpRequest
    With oRequest
        .Open "GET", sUrl, True
        .setRequestHeader "Content-Type", "application/json"
        .SetCredentials "domain\administrator", "pw", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
        .send
        .waitForResponse
        sResult = .responseText
        Debug.Print sResult
        sResult = oRequest.Status
        Debug.Print sResult
    End With
    End Sub

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, February 20, 2017 6:43 AM

All replies

  • Can't give you specific example but since REST API calls will be kind of same you may refer here

    http://ramblings.mcpher.com/Home/excelquirks/json/rest

    https://github.com/VBA-tools/VBA-Web

    Thanks

    Kislay

    Mark as answer if it helps you

    Friday, February 17, 2017 1:45 PM
  • Hi,

    You could check below sample code for your reference, hope it helps you.

    Sub RESTCall()
        Const sUrl As String = "http://sp:12001/_api/Web/Lists/getByTitle('Parent')/items(1)"
        Dim oRequest As WinHttp.WinHttpRequest
        Dim sResult As String
        
        Set oRequest = New WinHttp.WinHttpRequest
    With oRequest
        .Open "GET", sUrl, True
        .setRequestHeader "Content-Type", "application/json"
        .SetCredentials "domain\administrator", "pw", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
        .send
        .waitForResponse
        sResult = .responseText
        Debug.Print sResult
        sResult = oRequest.Status
        Debug.Print sResult
    End With
    End Sub

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, February 20, 2017 6:43 AM