search

gramener / geocode-excel

Convert addresses to lat-longs (and vice versa) in Excel
48 stars 22 forks source link

User-define type not defined #3

Closed Krukarius closed 4 years ago

Krukarius commented 4 years ago

Hi,

I would like to use your tool for Nominatim, OpenStreetMap Api.

I was trying to use your code, copying it from the source file, which looks like this:

Function NominatimGeocode(address As String) As String Application.Caller.Font.ColorIndex = xlNone Dim xDoc As New MSXML2.DOMDocument xDoc.async = False xDoc.Load ("https://nominatim.openstreetmap.org/search?format=xml&q=" + address) If xDoc.parseError.ErrorCode <> 0 Then Application.Caller.Font.ColorIndex = vbErr NominatimGeocode = xDoc.parseError.reason Else xDoc.SetProperty "SelectionLanguage", "XPath" Dim loc As MSXML2.IXMLDOMElement Set loc = xDoc.SelectSingleNode("/searchresults/place") If loc Is Nothing Then Application.Caller.Font.ColorIndex = vbErr NominatimGeocode = xDoc.XML Else Application.Caller.Font.ColorIndex = vbOK NominatimGeocode = loc.getAttribute("lat") & "," & loc.getAttribute("lon") End If End If End Function

but after typing the formula into my Excel document: =nominatimGeocode(AA2) I am getting an error: **Compile error

User-defined type not defined**

I came to consultation, that you used your own API key, so it might cause the problem. I set up my own API key on the Mapquest.com, and receive it.

Now I don't know, where to place it. I superseded the line: Dim loc As MSXML2.IXMLDOMElement, which was indicated by the VBA excel debugger. I put my API key like this: Dim loc As (myAPI). Unfortunately now, the debugger points the same line, and error says Syntax error.

Could you help me to sort it out?

Thank you in advance,

sanand0 commented 4 years ago

@Krukarius

We haven't yet written a geocoder for MapQuest. It isn't difficult, though. Could you please email me at s.anand@gramener.com? We can get on a call and figure it out.

nietosac commented 4 years ago

I'm somewhat a newbie in GIS/geocoding so I wouldn't be much help on figuring it out, so I'll just follow your progress via update from git hub. Stay safe,

Ed

On Wed, Apr 29, 2020 at 9:24 AM S Anand notifications@github.com wrote:

@Krukarius https://github.com/Krukarius

We haven't yet written a geocoder for MapQuest. It isn't difficult, though. Could you please email me at s.anand@gramener.com? We can get on a call and figure it out.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/gramener/geocode-excel/issues/3#issuecomment-621319311, or unsubscribe https://github.com/notifications/unsubscribe-auth/AF2KEDXZUOVFWL6MU66NVNDRPBIEBANCNFSM4MP6TI5A .

Krukarius commented 4 years ago

The issue has been solved.

Since the VBA Excel debugger shows:

Dim loc As MSXML2.IXMLDOMElement

and states an error User-defined type not defined

It means, that the problem lies in the lack of a proper library.

In this event everyone, who use this tool in his own workbook must do the following steps:

  1. In your VisualBasic main top ribbon - select Tools -> References -> switch on (add to the library) Microsoft XML. v.3.0 -> click OK
  2. Try again, and it should work
sanand0 commented 4 years ago

Thanks a lot for sharing this, @Krukarius -- sorry I wasn't able to revert earlier.