According to eggheadcafe, Excel doesn't have built-in maps anymore. I'm not a charting guy, as you know, so I didn't know that. I also didn't know that they ever did. But I wanted a map of the US with sales by region. That seems possible, but all I got this time was a map of Nebraska. Kind of a proof of concept before I'm ready to tackle the whole US. Here's how I got it:
First I went to Finder to get a KML file. A KML file appears to be a special purpose XML file. You know XML? That data format that's perfect for everything in every situation? When I got to Finder, I noted a CSV download. Perfect, I thought. Alas, the CSV does not provide the same information as the KML file, so I had to go the long way. I'm not sure what good that CSV file is. That is, I don't really know what you could make out of that data.
I downloaded the KML file and started inspecting it. There is a coordinates tag that plots the outline of every state. Some states, like Nebraska, have one coordinates tag because we are a closed polygon of a state. States that touch water, however, have more than one coordinates tag because there's always an island. Alaska has ten million coordinates tags (not really, but it's a lot).
If I had Office 2003 Professional, I could probably get that XML file right into a spreadsheet. But I don't. I have Small Business Edition - no XML facilities in here. Although I intended (and intend) to make a map of the US, I thought I'd start simply and just do one state - my state. I copied the coordinates tag with all of those coordinates and pasted into a text file. Then I used this macro to list them in a worksheet.
Sub GetCoordinates()
Dim sFname As String
Dim lFnum As Long
Dim sLine As String
Dim vaCoords As Variant
Dim vaIndiv As Variant
Dim i As Long
lFnum = 1
sFname = Environ$("userprofile") & "\My Documents\NE_Coordinates.txt"
Open sFname For Input As lFnum
Do While Not EOF(lFnum)
Line Input #lFnum, sLine
vaCoords = Split(sLine, " ")
For i = LBound(vaCoords) To UBound(vaCoords)
vaIndiv = Split(vaCoords(i), ",")
Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 2).Value = vaIndiv
Next i
Loop
Close lFnum
End Sub
I actually tried pasting the coordinates from the XML (opened in Firefox) directly into Excel and splitting them out with Text to Columns and some transposing, but I think I ran into a character limit - not everything pasted. So I went with the text file/VBA method.
With the data in Excel, I created an XY Scatter Chart. I removed the axis, gridlines, makers, and connected the data points with lines.
Not too bad. I'm not sure how to ensure the axes are on the same scale. This looks a little horizontally squashed. Here's what it looks like before the clean up.
I need to read the XML file directly into VBA. There are some XML libraries (under VBE - Tools - Reference) that look promising, but I haven't tried them yet. Then I should be able to draw the whole country without a lot of effort.
