Trying to integrate an HTTP GET request in my MS-A

问题:

I want to import data from Anedot, a credit card processing firm, using a HTTP GET request from an MS Access program. Anedot uses a RESTful API and has provided help on there website: https://anedot.com/api/v2

I want to do this with VBA, and associate the import with a button on an MS Access form. I've read that this only possible with XML. Do I create the XML file with VBA?

I'd greatly appreciate some background information on how to get this done, as most of it is flying over my head. I don't really know where to begin and I'm having trouble finding anything useful on google.

So far I've realized I'll need to reference their API via a URL link (which they provide), and that I'll have to authorize my account using my username and a token ID. But how can I do this in VBA?

Thanks.

回答1:

First of all try to make a request to API using basic authorization. Take a look at the below code as the example:

Sub Test()

    ' API URL from https://anedot.com/api/v2
    sUrl = "https://api.anedot.com/v2/accounts"
    ' The username is the registered email address of your Anedot account
    sUsername = "[e-mail]"
    ' The password is your API token
    sPassword = "1e56752e8531647d09ec8ab20c311ba928e54788"
    sAuth = TextBase64Encode(sUsername & ":" & sPassword, "us-ascii") ' bXltYWlsQGV4YW1wbGUuY29tOjFlNTY3NTJlODUzMTY0N2QwOWVjOGFiMjBjMzExYmE5MjhlNTQ3ODg=
    ' Make the request
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sUrl, False
        .SetRequestHeader "Authorization", "Basic " & sAuth
        .Send
        Debug.Print .ResponseText
        Debug.Print .GetAllResponseHeaders
    End With

End Sub

Function TextBase64Encode(sText, sCharset) ' 05 10 2016
    Dim aBinary
    With CreateObject("ADODB.Stream")
        .Type = 2 ' adTypeText
        .Open
        .Charset = sCharset ' "us-ascii" for bytes to unicode
        .WriteText sText
        .Position = 0
        .Type = 1 ' adTypeBinary
        aBinary = .Read
        .Close
    End With
    With CreateObject("Microsoft.XMLDOM").CreateElement("objNode")
        .DataType = "bin.base64"
        .NodeTypedValue = aBinary
        TextBase64Encode = Replace(Replace(.Text, vbCr, ""), vbLf, "")
    End With
End Function

Put your credentials to sUsername and sPassword variables, choose the appropriate URL from API help page and put it to sURL. Then you can parse JSON response from the server (currently you will see the response for /v2/accounts request in Immediate window).

回答2:

It's a fairly lengthy question to be honest, but lets start with some code to get you going.

This Class Module ("clsXMLHttpMonitor") should help:

Option Explicit


Dim XMLHttpReq As MSXML2.ServerXMLHTTP

Dim RequestedVar As String
Dim P As Object

Public Sub Initialize(ByVal uXMLHttpRequest As Object, Optional RequestedValue As String = "")
   RequestedVar = RequestedValue
   Set XMLHttpReq = uXMLHttpRequest
End Sub

Sub ReadyStateChangeHandler()
    If XMLHttpReq.ReadyState = 4 Then
        If XMLHttpReq.Status = 200 Then
            'Process the response here
            Debug.Print "200 recieved"
            Set P = JSON.parse(XMLHttpReq.responseText)
        Else
            If XMLHttpReq.Status = 404 Then
                'Handle it
            End If
        End If
    End If

End Sub

Function returnResponseHeaders() As String
returnResponseHeaders = XMLHttpReq.getAllResponseHeaders
XMLHttpReq.Send
End Function

Function returnFullText() As String
If XMLHttpReq.ReadyState = 4 Then
    If XMLHttpReq.Status = 200 Then
        returnFullText = XMLHttpReq.responseText
    Else
        returnFullText = "-1"
    End If
Else
    returnFullText = ""
End If
End Function

End Function

Use it like this:

Set XMLHttpReq = New MSXML2.ServerXMLHTTP
Set XMLHttpMon = New clsXMLHttpMonitor
XMLHttpMon.Initialize XMLHttpReq
XMLHttpReq.OnReadyStateChange = XMLHttpMon
XMLHttpReq.Open "POST", URL, True
XMLHttpReq.Send strPayload

回答3:

As you seem to request a Json response from a URL, you can study the Json modules here for a full implementation that collects the Json response in a collection, which you then can use in your code or save to a table. See the demo module for examples:

VBA.CVRAPI