VBA: GET and POST method

[umamaheswaranp]

Hi, I am struck with these two requirements and i couldn't file a solution. I need help please.

* Through VBA, i need to trigger this URL ("https://myserver/connect/login?api_key=xxx").
A successful login on the same redirect URL, i get two parameters; status=success & request_token=yyyy

1. How to capture redirect URL and how to store status and request_token details in a cell.

* After obtaining the success message and request_token from the login flow, i should POST it to this URL(“https://myserver/session”) with these request parameters; request_token=abc, name=xyz, email=xy@xy.com. In response i get the output as JSON.

2. How to send multiple parameters and receive response.

Thanks

 

[Kenneth Hobson]

Welcome to the forum! Without an actual site to test, it is hard to help.

Search for this as it will show links with example data: excel winhttp json

 

[John_w]

You can do this with WinHttpRequest - set a reference to Microsoft WinHTTP Services v5.1.

Something like this to capture the URL redirect:

    Dim HTTPreq As WinHttpRequest, redirectURL As String
    Set HTTPreq = New WinHttpRequest
    With HTTPreq
        .Open "GET", URL, False   'or POST maybe
        .Option(WinHttpRequestOption_EnableRedirects) = False  'disable automatic redirect
        .setRequestHeader "xxx", "yyy"  'set required request headers
        .Send        
        If .Status = 302 Then  'http redirect code
            redirectURL = .getResponseHeader("Location")   'get redirect URL from Location header
        End If
    End With

You send multiple parameters as form data in a POST request:

    Dim formData As String
    formData = "request_token=abc&name=xyz&email=" & URLEncode("xy@xy.com")
    With HTTPreq
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .setRequestHeader "other", "headers"
        .Send (formData) 
        Debug.Print .responseText
    End With

You have to URL encode the parameters and their values, e.g. for the email address because it contains the "@" character. See How can I URL encode a string in Excel VBA? - Stack Overflow.

 

[John_w]

PS - The second code is missing the POST request:

With HTTPreq
    .Open "POST", URL, False