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.
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.
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