Extracting data from json api

[limwei]

i am trying to get the fare required to travel from one point to another point by extracting the data "tr" from a json API under total_data but i cant seem to get it. i just started learning this vba thingy so sorry for the lack of knowledge if i said anything that isnt correct. Below you can find the function and the url i am requesting from.

This is my function:


Function PAS(origin, destination)
Dim strUrl As String
strUrl = "https://www.streetdirectory.com/api/?mode=journey&output=json&country=sg&q=" & origin & "%20to%20" & destination & "&methods=bustrain&vehicle=both&info=1&date=06/20/2021&time=01:38%20PM"
Set httpReq = CreateObject("MSXML2.XMLHTTP")
With httpReq
.Open "GET", strUrl, False
.Send
End With
Dim response As String
response = httpReq.ResponseText
Debug.Print response
Dim parsed As Dictionary
Set parsed = JsonConverter.ParseJson(response)
Dim dollar As Double
For Each leg In parsed("total_data")
dollar = dollar + leg("tr")
Next leg
PAS = dollar
End Function

The URL i am requesting from:
https://www.streetdirectory.com/api/?mode=journey&output=json&country=sg&q=629649%20to%20828732&methods=bustrain&vehicle=both&info=1&date=06/20/2021&time=01:38%20PM

[p45cal]

Would a non-vba approach be any good?
In the attached is a table at B2 which you can add to/amend.
Right-click the table at cell F5 and choose Refresh to update it.
The first time you may be asked about privacy, choose to ignore privacy concerns (for now at least).
With your given url there only seems to be one leg and I've grabbed the one tr value, if you were to provide another url which has multiple legs I could tweak.
There's a lot more different kinds of information extractable too; to the right of column S are 4 more tables (hard coded to your url), as well as a linear distance of 27649.216536106.

[Kenneth Hobs]

Welcome to the forum!

I guess I would also need example inputs for multiple "legs". For one leg using VBA:

Sub Test_PAS()
MsgBox PAS([a2], [b2])
End Sub

'=pas(A2,B2)
Function PAS(origin, destination)
Dim strUrl As String, dollar As Double, response As String, parsed As Object, httpReq As Object

strUrl = "https://www.streetdirectory.com/api/?mode=journey&output=json&country=sg&q=" & _
origin & "%20to%20" & destination & "&methods=bustrain&vehicle=both&info=1&date=06/20/2021&time=01:38%20PM"

Set httpReq = CreateObject("MSXML2.XMLHTTP")
With httpReq
.Open "GET", strUrl, False
.Send
End With
response = httpReq.ResponseText
'JSONLib, http://code.google.com/p/vba-json/
Set parsed = JsonConverter.ParseJson(response)
dollar = parsed("total_data")("tr")

PAS = dollar
End Function

[limwei]

Would a non-vba approach be any good?
In the attached is a table at B2 which you can add to/amend.
Right-click the table at cell F5 and choose Refresh to update it.
The first time you may be asked about privacy, choose to ignore privacy concerns (for now at least).
With your given url there only seems to be one leg and I've grabbed the one tr value, if you were to provide another url which has multiple legs I could tweak.
There's a lot more different kinds of information extractable too; to the right of column S are 4 more tables (hard coded to your url), as well as a linear distance of 27649.216536106.

[limwei]

Thats exactly what i need, thank you so much!! much appreciated

[limwei]

[QUOTE=Kenneth Hobs;408523]Welcome to the forum!

I guess I would also need example inputs for multiple "legs". For one leg using VBA: