CODEWITHSUNDEEP
excelvbaurlbing-mapsbing-api
DonPromillo
DonPromillo

Reputation: 1

Using BING Maps API to get the cords of a location

I've been using my VBA Code to get the coordinates from different locations whcih is in the red by a JsonConverter. Right now, I get an error when the JSON Converter tries to read the output from the URL.

"This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation"

 ' Set your Bing Maps API key
apiKey = "myKey"

' Build the API request URL
url = "https://dev.virtualearth.net/REST/v1/Locations/" & _
      Replace(address, " ", "%20") & "?key=" & apiKey

'Debug.Print url

' Send the API request
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", url, False
    .send
    responseText = .responseText
End With`

-> sample URL: https://dev.virtualearth.net/REST/v1/Locations/Lerchenauer%20Straße%2076,%2080809%20München?key=

I checked my BING Maps key, which is active and I never changed..

Upvotes: 0

Views: 348

Answers (1)

rbrundritt
rbrundritt

Reputation: 17954

The "error" you are seeing "This API cannot be accessed..." is part of the copyright text. You are getting the response from Bing Maps fine, you are just looking at the wrong part of the response. Note that the response is in JSON format.

Note that geocoding addresses in Excel with VBA has been done a lot over the past decade and there are a ton of blogs out there on how to do this. I highly recommend following one of those rather than trying to write something from scratch. For example: https://ramblings.mcpher.com/integratingexcelwithmapsand-earth/complete-excel-address-data-with-bing-maps-api/

That said, VBA is really old and there are newer ways to create add-ons/scripts for Excel: https://learn.microsoft.com/en-us/office/dev/scripts/resources/vba-differences Note that some companies don't allow macro's to be run for security purposes. Using a more modern method for creating add-ons would make your work more reusable across both desktop and web versions of Excel.

Also consider using Azure Maps instead of Bing Maps. Azure Maps is Microsoft's "newer" map platform (released 5 years ago). It's v2 search/geocoding services have the same data as Bing Maps. Using Azure Maps would likely ensure that your code will work the longest.

Upvotes: 0

Related Questions