VBA: How to Code a Polygon Coordinates Importer

Somehow, a few weeks ago, no doubt as a result of my having enough caffeine in my system to  down a small hippopotamus, I thought it would be a good idea to make a KML to CSV exporter in AutoHotkey. I can’t even claim ignorance this time—I knew VBA was a thing. But the neurons firing in my brain blazed right passed the LOGIC center and crashed head on into the AHK FIXES EVERYTHING node.

New Rule:
Don’t default to whatever language you’re most comfortable with. Unless it’s to get around using Scheme because Scheme is a bastard.

Since I’m using the coordinates in Excel, this weekend I’m adapting the AHK to live in a nice little VBA button. One I can use to get a summary of the polygon’s northernmost and southernmost point. East and west too.

Ok. So what’s different?

Luckily, all the logic from the AHK still applies. In human-speak, we want the computer to prompt the user for a kml file to load, and then we want to push all that text into a variable that we can chop up like sushi. Excel Easy has the best tutorial on how that’s done because whoever runs that place is clearly a saint.

This script is slightly different in that it’s tailored to chop up a polygon, not straight points. First, it scoops out the name and throws everything else from file into a loop that cuts out the coordinates for display in a worksheet called “Polygons.”

Thanks to CuriousJatin, I now know you can save a desired worksheet into a variable for quick reference using these lines.

Dim worksheetVariable As Worksheet
Set worksheetVariable = ActiveWorkbook.Worksheets(“Name of Worksheet“)

Really helpful if you want to copy/paste things from different sheets in the same workbook.

I was going to do a walk through of all the new functions I had to learn to make it work, but in the interest of saving time, I’m posting my VBA cheatsheet.

The Code

You have to have a worksheet in your workbook named “Polygons” before running this. That’s where Excel is going to dump your coordinates.

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 file
KmlTxtCopy = KmlFileLoc & ".txt"
FileCopy KmlFileLoc, KmlTxtCopy

Open KmlTxtCopy For Input As #1
'EOF stands for End of File
Do 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 = ActiveWorkbook.Worksheets("Polygons")

Dim l As Integer
l = 1
Do While InStr(CoordVals, ",") > 1
CommaPos = InStr(CoordVals, ",")
Longitude = Left(CoordVals, CommaPos - 1)
pwb.Cells(l + 1, 1).Value = Longitude
'Trim out that Longitude excluding the comma
CoordVals = Right(CoordVals, Len(CoordVals) - CommaPos)
'Do the Same for Latitude but in 2nd Column
CommaPos = InStr(CoordVals, ",")
Latitude = Left(CoordVals, CommaPos - 1)
pwb.Cells(l + 1, 2).Value = Latitude
CoordVals = Right(CoordVals, Len(CoordVals) - CommaPos - 1)
pwb.Cells(l + 1, 3).Value = PName
l = l + 1
Loop