Making a VBA API Request with Auth and 2 Headers?

Question

I have the following code in Python and want to move the request over to VBA. How do I write this in VBA?

import requests
import json

url = "https://us.market-api.kaiko.io/v1/data/order_book_snapshots.v1/exchanges/drbt/option/btc26jun2010000c/snapshots/full?" \
      "start_time=2020-05-29T17:33:00.000000Z&end_time=2020-05-29T17:33:10.000000000Z"

payload = {}
headers = {
  'Accept': 'application/json',
  'x-api-key': '123456'
}

response = requests.request("GET", url, headers=headers, data = payload)
result = json.loads(response.text)

There are plenty of posts here covering how to make a GET request from VBA. Please take a look at some of those. — Tim Williams, Jun 04 '20 at 20:30
Yes, for simple GET requests I see. But I don't see support for examples with headers — user1781336, Jun 12 '20 at 03:04
eg - https://stackoverflow.com/questions/59645875/msxml2-xmlhttp-fails-to-send-request-cookie-header — Tim Williams, Jun 12 '20 at 04:29
@TimWilliams please see my response and question in comment below — user1781336, Jun 12 '20 at 20:46

user1781336 · Accepted Answer · 2020-06-14T02:04:46.503

1

Public Sub testAPI()
Dim http As Object
Dim JSON As Object


Dim pm As Worksheet
Set pm = Sheets("Params")

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", ("https://us.market-api.kaiko.io/v1/data/order_book_snapshots.v1/exchanges/drbt/option/btc26jun209000p/snapshots/full?start_time=2020-05-29T17:33:00.000Z&end_time=2020-05-29T17:33:30.0Z"), False
http.SetRequestHeader "Accept", "application/json"
http.SetRequestHeader "x-api-key", "1234"

http.Send
Set JSON = ParseJson(http.ResponseText)

pm.Cells(2, 8).Value = JSON("data")(1)("asks")(1)("price")


End Sub

edited Jun 14 '20 at 02:04

answered Jun 12 '20 at 19:09

user1781336

85
4
12

@TimWilliams when I run the above I get "Invalid Procedure Call or Argument". I think I am calling the correct parsed Json format? The result format is given here: https://docs.kaiko.com/#order-book-snapshots-full – user1781336 Jun 12 '20 at 20:41
1

"asks" will be parsed to a VBA collection object: you can't assign a collection to a cell value. `pm.Cells(2, 8).Value = JSON("data")(1)("asks")(1)("amount")` (for example) should work assuming you got the expected response. – Tim Williams Jun 12 '20 at 22:09
When I use pm.Cells(2, 8).Value = JSON("data")(1)("asks")(1)("amount") it still gives "invalid Procedure Call or Argument". I put JSON, from the row above it, in the Expression-Watchlist. When I put a breakpoint at the pm.Cells(2,8).Value row I see data in the Watchlist for Item4 but there is nothing to expand. Is that a contributing issue? – user1781336 Jun 12 '20 at 22:35
I can't see the actual response, so it's difficult for me to say what the problem might be. Are you using this library to parse the JSON - https://github.com/VBA-tools/VBA-JSON ? – Tim Williams Jun 12 '20 at 22:38
Yes, I am using that library to parse the JSON – user1781336 Jun 13 '20 at 01:04
That should work based on the sample response on the API page. – Tim Williams Jun 13 '20 at 01:23

Making a VBA API Request with Auth and 2 Headers?

1 Answers1