PDA

View Full Version : VBA API Parsing and pasting json string to excel spreadsheet. run time 13 error



vmellbin
03-21-2018, 06:45 AM
Hello i am trying to gather data from e-conomic and paste it into an excel spreadsheet.

Sub Economic()
Const sUrl As String = "https://restapi.e-conomic.com/accounting-years/2018/totals?skippages=0&pagesize=1000"
Dim oRequest As Object
Dim i As Integer


Set oRequest = CreateObject("MSXML2.serverXMLHTTP")
With oRequest
.Open "GET", sUrl, True
.setRequestHeader "Content-Type", "application/json; charset=UTF-8"
.setRequestHeader "X-AppSecretToken", "Demo"
.setRequestHeader "X-AgreementGrantToken", "Demo"
.Send
.WaitForResponse
Stest = oRequest.responseText
MsgBox (Stest)
Dim oJSON As Object
Set oJSON = JsonConverter.ParseJson(Stest)
End With
i = 2
For Each item In oJSON
Sheet1.Range(i, 1).Value = item("totalInBaseCurrency")
i = i + 1
Next
End Sub

I am getting a run-time error '13': Type mismatch and it is highlighting the line Sheet1.range(i,1).Value = Item("totalInBaseCurrency")
any help would be greatly appreciated. Sorry for the formatting I am relatively new to VBA api formatting.

p45cal
03-21-2018, 12:00 PM
I think we'd need to know a bit more about this:
JsonConverter.ParseJson
There'll be some code somewhere that you're using; can you point us to which version you're using and where to get it from?