VBA GET And POST Examples

Admin
Post Views: 374
Print Friendly, PDF & Email

VBA GET And POST Examples

Just a quick post showing how to use VBA to make REST API calls, these examples are making calls to an AWS End Point, which requires an API key.

Get

The Get method, Gets all rows from the database.

Sub CATMain()

    Const APIkey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")

    Dim rootURL As String, EndpointURL As String

    rootURL = "https://xxxxxxxxx.execute-api.eu-west-1.amazonaws.com"
    EndpointURL = rootURL & "/Prod/v1/xxxxxxxxxxxxx"
    
    With httpReq
        .Open "GET", EndpointURL, False
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "x-api-key", APIkey
        .send
        Debug.Print .Status, .statusText
        Debug.Print .responseText
    End With
    
End Sub

Post

The Post method also takes in a JSON string, this could be loaded from a file.

Sub CATMain()
    
    Dim Json As String
    
    Json = "{" & Chr(34) & "macroName" & Chr(34) & " : " & Chr(34) & "GreatestMacroEver" & Chr(34) & _
            " , " & Chr(34) & "macroRevision" & Chr(34) & " : " & Chr(34) & "Z.1" & Chr(34) & _
            " ," & Chr(34) & "userName" & Chr(34) & ": " & Chr(34) & "ruby.murry" & Chr(34) & _
            " ," & Chr(34) & "computerName" & Chr(34) & ": " & Chr(34) & "abc-rmurry" & Chr(34) & _
            " ," & Chr(34) & "partNumber" & Chr(34) & ": " & Chr(34) & "PN012345678" & Chr(34) & _
            " ," & Chr(34) & "partRevision" & Chr(34) & ": " & Chr(34) & "B.2" & Chr(34) & _
            "}"
    
    Const APIkey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")

    Dim rootURL As String, EndpointURL As String

    rootURL = "https://xxxxxxxxxx.execute-api.eu-west-1.amazonaws.com"
    EndpointURL = rootURL & "/Prod/v1/xxxxxxxxxxxxxx"
    
    With httpReq
        .Open "POST", EndpointURL, False
        .setRequestHeader "Content-type", "application/json"
        .setRequestHeader "x-api-key", APIkey
        .send (Json)
        Debug.Print .Status, .statusText
        Debug.Print .responseText
    End With
    
End Sub

Patch and Delete

Patch and Delete in this case are identical to Post except we must change the REST verb, from POST to PATCH or DELETE.