Use VBA to Get Sharepoint List information

Article
2018-04-02

Question

_{Monday, April 2, 2018 2:37 PM}

Hi,

I'm trying to use REST to get sharepoint LIST info for particular key in excel VBA. It seems it's working on my pc even without credentials if I pre-signed into the sharepoint in IE. However, it's not working in all my teammates' PC, it constantly throws "403 FORBIDDEN" or this error "{"odata.error":{"code":"-2147024891, System.UnauthorizedAccessException","message":{"lang":"en-US","value":"Access denied. You do not have permission to perform this action or access this resource."}}}
{"co".

I assume this is an authentication issue, however after hours of debugging for different solutions, it still doesn't working, anyone could throw a light will be highly appreciated.

My VBA code method 1 without passing credentials into:

(this do works if I pre login into the sharepoint, but not working in all others pc even they pre-login into the sharepoint)

Sub Verify_RAROC_Version()
    Dim xmlObj As Object
    Dim sUrl As String
    Dim retVal As String
    Dim isHardCodedParsingVersion As Boolean

    isHardCodedParsingVersion = True
    


    sUrl= "https://xxx.sharepoint.com/sites/ABC/_api/web/lists/GetByTitle('latest_version_test')/items"
    Set xmlObj = CreateObject("MSXML2.XMLHTTP.6.0")

    
    xmlObj.Open "GET", sUrl, False
    
 
    'parse return value as json format
    xmlObj.SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
    xmlObj.SetRequestHeader "Accept", "application/json"
    
    
    xmlObj.Send
    
    
    retVal = xmlObj.responseText
    Debug.Print retVal
    
    If isHardCodedParsingVersion = True Then    
        Dim versionKeyPosAs Integer
        Dim versionNum As String
        
        'Try to find the substring version Key RAROC_Version from the full string
        versionKeyPosAs = InStr(retVal, "Latest_Version")
        'Debug.Print versionKeyPosAs 
        
        'hardcoded to get substring position of the Key in the list and parsing the version from there
        versionNum = Mid(retVal, versionKeyPosAs + 16, 4)
        Debug.Print versionNum
        
        Worksheets("Main").Range("H17") = versionNum
    Else
        ' TODO: to be implemented
        Debug.Print "Hello World! to be implemented..."
    End If
End Sub

My VBA code second try by passing the credentials into the REST get method:

Sub GetFromWebpage()
    
    Dim url As String
    Dim objWeb As Object
    Dim strXML As String
    
    ' Instantiate an instance of the web object
    Set objWeb = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    objWeb.Open "GET", url, False, "[email protected]", "helloworld"
    objWeb.SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
    objWeb.SetRequestHeader "Accept", "application/json"     
    
    objWeb.Send
    
    
    ' Look at the HTML string returned
    strXML = objWeb.responseText
    Debug.Print strXML
    
    
    
End Sub

Third try using different way to passing credentials:

Sub Third_Try()
    Dim response As String
    Dim sUrl As String
    
    Dim userName As Variant
    Dim password As Variant
    Dim versionNum As String
    Dim rAROCVersionPos As Integer

    Dim xmlObj As Object
    Dim testStr As String 'ServerXMLHTTP
    Set xmlObj = CreateObject("MSXML2.XMLHTTP.6.0") 
    userName = "[email protected]" 
    password = "helloworld" 

    sUrl = "https://abc.sharepoint.com/sites/XYZ/_api/web/lists/GetByTitle('Latest_version')/items"


    With xmlObj
        .Open "POST", sUrl, False
        .SetRequestHeader "Content-type", "application/x-www-form-urlencoded"
        .Send "userlogin=true&user=" & userName & "&pass=" & password & "&authcode=false"
        .Open "GET", sUrl, False
        .SetRequestHeader "Content-type", "text/xml"
        .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .SetRequestHeader "Accept", "application/json"
        .Send
    End With

    response = xmlObj.responseText

    Debug.Print response

Last try by using a different way to passing credentials: (still not working... : ( )

Sub Third_Try()
    Dim response As String
    Dim sUrl As String
    
    Dim userName As Variant
    Dim password As Variant
    Dim versionNum As String
    Dim rAROCVersionPos As Integer

    Dim xmlObj As Object
    Dim testStr As String 'ServerXMLHTTP
    Set xmlObj = CreateObject("MSXML2.XMLHTTP.6.0") 
    userName = "[email protected]" 
    password = "helloworld" 

    sUrl = "https://abc.sharepoint.com/sites/XYZ/_api/web/lists/GetByTitle('Latest_version')/items"


    With xmlObj
        .Open "GET", sUrl, False
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .setProxyCredentials userName, password
        .Send
    End With

    response = xmlObj.responseText

    Debug.Print response

Need help, thanks so much!!

All replies (4)

_{Tuesday, April 3, 2018 10:19 AM}

Hi,

The following code for your reference:

Sub RESTCall()
    Const sUrl As String = "http://sp2013/_api/Web/Lists/getByTitle('Test')/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

If you want to get list items from SharePoint Online, please check the authentication as the link below:

http://paulryan.com.au/2014/spo-remote-authentication-rest/

Best Regards,

Dennis

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

_{Tuesday, April 3, 2018 12:32 PM}

Thanks a ton Dennis!

Let me have a try on your method.

_{Tuesday, April 3, 2018 12:53 PM}

Hi Dennis,

Seems i'm not a lucky guy... : (

I'm still getting the same error.... Any idea?

(I replaced the administrator with my userid in sharepoint, pw with my password already...)

<?xml version="1.0" encoding="utf-8"?><m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><m:code>-2147024891, System.UnauthorizedAccessException</m:code><m:message xml:lang="en-US">Access denied. You do not have permission to perform this action or access this resource.</m:message></m:error>
403

_{Wednesday, April 4, 2018 8:11 AM}

Hi,

Please use the user account name like this: "domain\admin".

Please make sure this user have the permission of the SharePoint list.