1

I am building an API using FastAPI that must be accessible from Excel VBA. FastAPI's OAuth2 authentication mechanism requires me to send a "form-data" POST request, but I don't know how to do this using WinHTTPRequest in VBA.

I already have a function which get plain JSON from the API, but this is a GET function and I'm not sure where to specify the "form-data" part in the body, nor where to put the username and password key-value pairs.

Here is a simple VBA GET that handles some errors. How would I modify this to do a form-data POST with username and password fields?

Public Function getreq(url As String)
    Dim req As WinHttpRequest
    Dim JsonString As String
    Dim jp As Object
    Dim resp As String
    Dim errorstring As String

    Set req = New WinHttpRequest
    ' req.SetRequestHeaderxxx ?
    ' this is where auth will go via POST form-data username and password?
    req.Open "GET", url
    On Error GoTo errhand:
        req.Send
        resp = req.ResponseText
        If resp = "Internal Server Error" Then
            resp = "{'error': 'Internal server error'}"
        End If
        getreq = resp
    Exit Function

errhand:

    Select Case Err.Number
        Case -2147012894 'Code for Timeout
            getreq = "{'error': 'Request timeout'}"
        Case -2147012891 'Code for Invalid URL
            getreq = "{'error': 'Bad url'}"
        Case -2147012867 'Code for Invalid URL
            getreq = "{'error': 'Cannot establish connection'}"
        Case Else 'Add more Errorcodes here if wanted
            errorstring = "Errornumber: " & Err.Number & vbNewLine & "Errordescription: " & Error(Err.Number)
            getreq = "{'error': '" & errorstring & "'}"
    End Select
End Function
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Thomas Browne
  • 23,824
  • 32
  • 78
  • 121
  • Does this [VBA POST json to API](https://stackoverflow.com/questions/29015207/vba-post-json-to-api) help? Authentication credentials are usually sent via `SetRequestHeader "Authorization", "basic " + Base64Encode(PasswordnUsername)` if basic authentication is used. – Pᴇʜ May 19 '20 at 13:56
  • No because your example is content-type JSON and I need form-data. – Thomas Browne May 19 '20 at 14:43
  • Doesn't make much difference I think. Have a look here [XmlHttpRequest – Http requests in Excel VBA](https://codingislove.com/http-requests-excel-vba/) this is sending form data. – Pᴇʜ May 19 '20 at 14:46
  • 1
    Still I don't see any reason to stick with the WinHTTPRequest. Is there any? But check my answer below. Actually the structure is always very similar no matter if you end up using `WinHTTPRequest` or `MSXML2.ServerXMLHTTP.6.0` or `MSXML2.serverXMLHTTP`. – Pᴇʜ May 19 '20 at 20:00

1 Answers1

3

Instead of opening a GET request req.Open "GET", url you need to open a POST

req.Open "POST", url, False

Then you can use setRequestHeader to set which content type you are going to send and what you accept in return.

req.setRequestHeader "Content-Type", "multipart/form-data"
req.setRequestHeader "Accept", "application/xml"

For authorization you would also need to create a request header. This depends on what your API uses.

req.setRequestHeader "Authorization:", "Bearer " & EncodeBase64("Your-access-token")

Finally you send your request

req.send (YourData)

Have a look into this answer which shows how the data structure for multipart/form-data is generated.

If a WinHTTPRequest does not work I recommend to try the following

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
URL = "https:///commercial/payments-processing/v1/test/token"
objHttp.Open "POST", URL, False
objHttp.setRequestHeader "Content-Type", "multipart/form-data"
objHttp.setRequestHeader "Accept", "application/xml"
objHttp.setRequestHeader "Authorization:", "Bearer [Access Token]"
objHttp.send (strPaylodValue)
strResponseStatus = objHttp.Status
strResponseText = objHttp.ResponseText
strResponseText = CStr(strResponseText)
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73