excelbing-mapsexcel-365

Getting Bing Maps Elevation Data Through Webservice and FilterXML produces VALUE Error


This is in Excel 365.

I have a valid Bing Maps API key and have used similar formulas to get latitude and longitude data for addresses in spreadsheets.

Trying to do the same thing with elevation data doesn't want to work, however, and after consulting Bing's documentation and searching online, I can't see what I'm doing wrong.

=FILTERXML(WEBSERVICE("http://dev.virtualearth.net/REST/v1/Elevation/List?points={"&I1092&","&J1092&"}&key={"&BingKey&"}"),"//*")

BingKey is a named range containing the key, and columns I and J hold latitude and longitude values. I've tried replacing all references with literal values, but I still get an error. The Xpath is purposely set to return everything just to see if I can get the webservice part of the formula to work.

Can someone show me where I'm going wrong?


Solution

  • I see a couple of issues.

    1. You have curly braces around the lati/lon and key values, when concatenated, these are being added to the string, making those values invalid.
    2. The default response format is JSON and you are trying to parse it as XML, thus the error message in Excel that the format is incorrect. Add &output=xml to the URL and it works.

    Here is a modified version of the formula you provided.

    =FILTERXML(WEBSERVICE("http://dev.virtualearth.net/REST/v1/Elevation/List?points="&I1092&","&J1092&"&output=xml&key="&BingKey),"//*")
    

    enter image description here