Error In VBA Code

[Woody Pecker]

I'm very new to VBA & to be honest I'm not 100% what I'm doing.

I'm putting together a quick Excel sheet which will extract data from Google Maps for Drive Time in hours & minutes & Distance in miles.

Here's the code I currently have:-

Private Sub GetDrivingTime()
  Dim origin As String
  Dim destination As String
  Dim apiKey As String
  Dim url As String
  Dim httpRequest As New XMLHTTP60
  Dim jsonResponse As String
  Dim jsonObject As Object
  Dim distance As String
  Dim duration As String

  ' Replace with your own Google Maps API key
  apiKey = "YOUR_API_KEY_HERE"
 
  origin = "1600 Amphitheatre Parkway, Mountain View, CA"
  destination = "20 W 34th St, New York, NY 10001"
 
  url = "[URL]https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=[/URL]" & _
        origin & "&destinations=" & destination & "&key=" & apiKey

  With httpRequest
    .Open "GET", url, False
    .Send
    jsonResponse = .ResponseText
  End With
 
  ' Parse the JSON response using the JsonConverter library
  Set jsonObject = JsonConverter.ParseJson(jsonResponse)
 
  distance = jsonObject("rows")(1)("elements")(1)("distance")("text")
  duration = jsonObject("rows")(1)("elements")(1)("duration")("text")
 
  MsgBox "The driving distance is: " & distance & vbNewLine & "The driving time is: " & duration
End Sub

I have obtained my companies API Key, but I don't want to post on here for IT security purposes.

I have also been given another piece of software JsonConverter library, but when added & the VBA ran I get the following error message, so can anyone assist?
To use the JsonConverter library in your VBA code, you need to import the JsonConverter module into your Excel workbook. Here's how to do that:

1. Download the JsonConverter library from the GitHub repository: GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA
2. Extract the ZIP file to a location on your computer.
3. In Excel, go to the "Developer" tab and click on the "Visual Basic" button to open the VBA editor.
4. In the VBA editor, go to the "File" menu and select "Import File".
5. Navigate to the location where you extracted the JsonConverter library and select the "JsonConverter.bas" file.
6. The JsonConverter module should now be imported into your workbook and accessible in your VBA code.

Once you've imported the JsonConverter module, you can use the JsonConverter.ParseJson function in your code to parse the JSON response from the Google Maps API, as shown in the code example in my previous response.


"Private Function json_ParseObject(json_String As String, ByRef json_Index As Long) As Dictionary"

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Woody Pecker]

I've been messing around with this, and still can only get this far

 

[wella86]

Go to the vba editor and do this:

add VBA Reference under Tools -> References: "Microsoft Scripting Runtime"

that will do it.