Return co-ordinates from an external gml file

[TJJ]

Hi Gurus,

Need a bit of help with returning (latitude and longitude) values from an external GML file.

I've been trying to use index & match but can't work it out.

The macro or formula must:

a) look up the value in column A
b) read a .gml file that is located in the same directory as the
   excel file.
c) search for this value
   (located within the <point:ID> </point:ID> tags)
d) and return the values underneath
   (in the <gml:pos> </gml:pos> tags) to the
   latitude and longitude columns in the same workbook.

In effect, this is finding the latitude and longitude co-ordinates for value A.

Is it even possible to create one macro to do this process for multiple worksheets in the excel file?

I've uploaded a before and after excel file, as well as a sample .gml file.

Thanks!
TJJ

 

[Hui]

Lookup Point 2 doesn't exist in the GML File , So I assume you mean pt 3

 

[Hui]

Try the following code:

Sub Import_GML_File()

Dim MyData As String
Dim strData() As String
Dim myDir As String
Dim fName As String
Dim i As Integer
Dim j As Integer
Dim p1 As Integer
Dim p2 As Integer
Dim p3 As Integer
Dim Lastrow As Long

myDir = "C:\Users\Huis\Downloads\For Chandoo\"
fName = "GML filename1.gml"
Open myDir + fName For Binary As #1
  MyData = Space$(LOF(1))
  Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For j = 2 To Lastrow 'Row No. of Worksheet
  For i = 1 To UBound(strData, 1) 'Line in file
  sVal = ">" + Trim(Sheets("Workbook1").Cells(j, 1).Value) + "<"
  If InStr(1, strData(i), sVal, vbTextCompare) > 0 Then
  p1 = InStr(1, strData(i + 1), "pos>")
  p2 = InStr(1, strData(i + 1), " ")
  p3 = InStr(1, strData(i + 1), "</")
  Sheets("Workbook1").Cells(j, 2) = Mid(strData(i + 1), p1 + 4, p2 - (p1 + 4))
  Sheets("Workbook1").Cells(j, 3) = Mid(strData(i + 1), p2, p3 - p2)
  End If
  Next i
Next j
End Sub

See attached file:

 

[TJJ]

Thanks Hui,

Yes I made a small formatting error in the upload file.

This is perfect and exactly what I want, I will just change it to work with multiple sheets.

I will donate some cash to Chandoo as it saves me about 1 hr a week!

cheers.

TJJ