Dear sir,
Imports text file but it only import only single point. Can it import all the points as shown in sheet2.
Thank you.
Imports text file but it only import only single point. Can it import all the points as shown in sheet2.
Thank you.
Code:
Sub Import_Point()
Dim KmlFileLoc As String, text As String, textline As String
KmlFileLoc = Application.GetOpenFilename()
'Won't read a KML, so you gotta make it a text fileKmlTxtCopy = KmlFileLoc & ".txt"
FileCopy KmlFileLoc, KmlTxtCopy
Open KmlTxtCopy For Input As #1
'EOF stands for End of FileDo Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
posName = InStr(text, "<name> ")
posEndName = InStr(text, " </name>")
PName = Mid(text, posName + 6, posEndName - posName - 6)
posCoords = InStr(text, "coordinates") + 12
posEndCoords = InStr(text, "</coordinates")
CoordVals = Mid(text, posCoords, posEndCoords - posCoords)
Dim pwb As Worksheet
Set pwb = ActiveSheet
Dim l As Integer
l = 1
Do While InStr(CoordVals, ",") > 1
CommaPos = InStr(CoordVals, ",")
Longitude = Left(CoordVals, CommaPos - 1)
pwb.Cells(l + 2, 2).Value = Longitude
'Trim out that Longitude excluding the commaCoordVals = Right(CoordVals, Len(CoordVals) - CommaPos)
'Do the Same for Latitude but in 2nd ColumnCommaPos = InStr(CoordVals, ",")
Latitude = Left(CoordVals, CommaPos - 1)
pwb.Cells(l + 2, 1).Value = Latitude
CoordVals = Right(CoordVals, Len(CoordVals) - CommaPos - 1)
pwb.Cells(2, 1).Value = "Latitude"
pwb.Cells(2, 2).Value = "Longitude"
pwb.Cells(2, 3).Value = "Name"
pwb.Cells(l + 2, 3).Value = PName
l = l + 1
Loop
End Sub