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.
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.