VBA: How to Code a Polygon Coordinates Importer

vba-header

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.

ui

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.

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
Advertisements
Tagged , , , , , , , , ,

7 thoughts on “VBA: How to Code a Polygon Coordinates Importer

  1. ARJ says:

    ha you are through a coordinate phase . . .

    Liked by 1 person

  2. curiousjatin says:

    Hi, Can you post the files, I have a feeling this would be easier in Python – pandas and must faster? but the code is very elegant. I must say.

    Liked by 1 person

    • I wish I could post the file! But WordPress doesn’t let you upload KMLs or KMZs. I’ve heard Panda is best for problems like this but haven’t had a chance to try it out. I’ve got my eye on your blog, curiousjatin, in case you start up with Python tutorials 🙂

      Like

  3. curiousjatin says:

    I may take mich time to learn. But here is one of best i found . Data analysis in Python with pandas: https://www.youtube.com/playlist?list=PL5-da3qGB5ICCsgW1MxlZ0Hq8LL5U3u9y

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: