Is adress in a specific area

[HansV]

Did you intend to attach a file?

[Stefan_Sand]

well, i changed the file to the txt Extension, it is in the previous post... ups

[HansV]

When I try to open the file as an XML file in Excel, I get an error message stating that "End tag 'Folder' does not match the Start tag 'Placemark'..."

[Stefan_Sand]

ist better to open it with Notepad++ or the normal text Editor and save it again. The original Extension is a XML format from Google. I am unable to attach the original file format here, so i saved it from Notepad++ as a txt file. I attached another copy now.

[HansV]

Sorry, I can't do anything with that file either - same error message.

[HansV]

Perhaps it'd be better to parse the KML file directly instead of importing it into Excel. See Excel VBA, Get KML File Data (Google Earth API).

[Stefan_Sand]

Hi Hans,

here is the link to the complete file, provided from the open data initiative in Austria (You have a lot of file formats there to Import):

https://www.data.gv.at/datensatz/?id=e4 ... 604ba9ade5" onclick="window.open(this.href);return false;

[HansV]

I could import the KML file, and I noticed the problem, but I'm afraid I don't know how to solve it.

[Stefan_Sand]

Hi Hans,

can this be solved in VBA , using named ranges? I added an example Sheet, where I transformed the coordinates to columns, using a UDF from Andy Pope.

regards,
Stefan

[HansV]

I'll take a look at it later today.

[Stefan_Sand]

thanks, HAns

[HansV]

Try this function:

Code: Select all

Function GetGebiet(Xcoord As Double, Ycoord As Double) As String
    Dim wshKML As Worksheet
    Dim c As Long
    Dim n As Long
    Dim m As Long
    Dim arr As Variant
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Set wshKML = Worksheets("III")
    n = wshKML.Cells(2, wshKML.Columns.Count).End(xlToLeft).Column
    For c = 2 To n Step 2
        m = wshKML.Cells(wshKML.Rows.Count, c).End(xlUp).Row
        arr = wshKML.Range(wshKML.Cells(4, c), wshKML.Cells(m, c + 1)).Value
        If udfPtInPoly(Xcoord, Ycoord, arr) Then
            GetGebiet = wshKML.Cells(2, c).Value
            Exit For
        End If
    Next c
ErrHandler:
    Application.ScreenUpdating = True
End Function