CODEWITHSUNDEEP
excelvbapost
Mick
Mick

Reputation: 2898

Send POST request data using VBA (Excel)

Any help much appreciated. Im trying to send a request to recieve an access token in VBA.

The suggested format is curl --request POST --url 'https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token' --header 'content-type: application/x-www-form-urlencoded' --data grant_type=client_credentials --data client_id=CLIENT_ID_HERE --data client_secret=CLIENT_SECRET_HERE --data scope=https://tapi.dvsa.gov.uk/.default

But I want to send this using Excel VBA so far I have this, But I cant get the data to send

Set DvlaService = CreateObject("MSXML2.XMLHTTP")

DvlaService.Open "POST", "https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token", False
DvlaService.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
DvlaService.Data "grant_type=client_credentials"
DvlaService.Data "data-client_id=" & API_ID
DvlaService.Data "data-client_secret=" & API_Secret
DvlaService.Data "data-scope=" & "https://tapi.dvsa.gov.uk/.default"
DvlaService.send

Response = DvlaService.responseText

Im still very new to this , so any help or pointers would be great

Thanks

Upvotes: 0

Views: 314

Answers (1)

Tim Williams
Tim Williams

Reputation: 166605

When performing a POST you typically put the "data" in the Send line - there's no "Data" method.

More like this:

Sub Tester()
    
    Dim DvlaService As Object, data As String
    
    '...
    '...
    Set DvlaService = CreateObject("MSXML2.XMLHTTP")
    
    DvlaService.Open "POST", "https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token", False
    DvlaService.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    
    'construct the POST body: url-encode values and join with `&`
    data = "grant_type=client_credentials"
    data = data & "&data-client_id=" & UrlEncode(API_ID)
    data = data & "&data-client_secret=" & UrlEncode(API_Secret)
    data = data & "&data-scope=" & UrlEncode("https://tapi.dvsa.gov.uk/.default")
    
    DvlaService.send data
    
    Response = DvlaService.responseText
    
End Sub

Function UrlEncode(t As String)
    UrlEncode = Application.EncodeURL(t)
End Function

Upvotes: 1

Related Questions