Pull Latitude from XML and keep Double

[georgiboy]

Hi all,

Not been on here for a while (sorry), I am having a mind blank with the below code. I am trying to geocode a postcode with Here Maps API, I have managed to pull the value i need from the XML but it does not keep it's decimal places.

Obviously the decimal place of a Lat or Long is pretty important so i need to keep it.

Here is the piece of VBA i suspect is causing my issue:

Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """Latitude"".?([0-9]+)": regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
tmpval = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
MsgBox tmpval

tmpval is defined as double by the way, i think it has something to do with the replacement part but i can't be sure.

Any help will be appreciated

Thanks in advance
George

[Paul_Hossler]

What EXACTLY is matches(0)?

[Leith Ross]

Hello georgiboy,

Does the word latitude appear in quotes in the text? Do you need to preserve these quotes?

@Paul - A Match occurs when the text returns a successful pattern match. Only one will be returned per each execution of the RegEx as the Global property is set to FalseA SubMatch is usually indicated in the RegEx Pattern by a pair of parentheses. The indices are zero based making SubMatch(0) the first one in the pattern. In the pattern above, (0-9)+, indicates 1 or more numbers that follow the word Latitude with or with a decimal point.

[georgiboy]

Thank you both for taking the time to have a look for me.

I have only just come back to this to have a play and i have found the following:

I believe the below part was only allowing the numbers and symbols within it (square brackets), anything after a symbol was removed thus leaving me with a round number.

([0-9]+)
I thought i had a rounding issue with double but instead it was more of a string issue.

I have changed it to the below and it now pulls through every bit of the latitude.

([0-9.-]+)

I had to add the - also as it was not pulling the latitude if it was negative, this was due to the - symbol being first in the string so it was removing everything after the -

I had also converted this from json to xml and used the below, but i am happy now i know a little more about VBScript.RegExp.

Set oSeqNodes = oXml.SelectNodes("//NavigationPosition") For Each oSeqNode In oSeqNodes
Latitude = oSeqNode.SelectSingleNode("Latitude").Text ' lat
Longitude = oSeqNode.SelectSingleNode("Longitude").Text ' long
Next

Thanks again

George