0

I've been scouring the interwebs for days now looking for the solution to translating my API script into VBA to allow it to operate directly within an Excel file. I have managed to replicate the signature key that is produced in my python script. However I'm still hitting invalid credential errors as I believe i'm structuring the headers and parameters in the correct way.

HEADER_A,HEADER_B and HEADER_C are placeholders for the headers required as outlined in the documentation. The parameter "job_id" is also required.

Working python script:

signature = generateSignature(sigtag,key)
headers = {"HEADER_A": signature,
           "HEADER_B": timestamp,
           "HEADER_C": employee_id}
apirequest = requests.get(GEOPAL_BASE_URL + apicall,
                          headers=headers,
                          params={"job_id": job_id})
apifeedback = apirequest.json() 

I've been piecing together approaches to the problem, however i cannot find an example where someone has had to use both parameters and headers like I had to use with the requests library above.

Here is my progress so far in VBA:

Private Sub cmdgetjobinfo()
    Dim URL As String, timestamp As String, empID As String, key As String, method As String, signature As String, hash As String, params As String
    Dim objHTTP As Object: Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    Dim ws As Worksheet: Set ws = Worksheets("API")        
    URL = "https://app.addressofwebsite.com/"
    uri = "api/jobs/get"
    timestamp = Format(Now(), "ddd, DD MMM YYYY HH:MM:SS") & " +0000"
    empID = "715"
    method = "get"
    key = "abckey"

    hash = ComputeHMACSHA256(key, method & uri & empID & timestamp)
    signature = EncodeBase64(hash)
    params = ("job_id=testid")

    objHTTP.Open "GET", URL & uri, False
    objHTTP.setRequestHeader "Content-Type", "application/json", "Accept", "application/json"
    objHTTP.setRequestHeader "Authorization", "HEADER_A", signature,"HEADER_B", timestamp,"HEADER_C", empID
    objHTTP.send params
    strResult = objHTTP.responseText
    Worksheets("API").Range("A10:A10") = strResult
End Sub

Current output from script:

{"status":false,"tag":"api\/jobs","error_code":0,"error_message":"Invalid Credentials"}

I know the issue exists around the 'objHTTP.send' and 'objHTTP.setRequestHeader' lines. I've tried them all on separate requestheader lines, without the 'authorisation' tag and lots of other random things. Is anyone able to point out where I'm failing here?

Many many thanks for your time!

Peter Connolly
  • 121
  • 1
  • 1
  • 8
  • And I guess it is not a public API with an URL you can share? – QHarr Jul 20 '18 at 22:54
  • Hey QHarr - unfortunately not :/ I can try to answer any further queries you may have to try and get you more information if it will aid things though? – Peter Connolly Jul 21 '18 at 17:30

0 Answers0