Another J-Walk YouTube playlist. These songs have US Cities in the title (except for two). I created a map of these cities using Excel and BatchGeo.com.
First, I went to the playlist and copied all of the songs.
Then I pasted it into Excel
Each picture is a hyperlink, so I ran some code to extract it
|
1 2 3 4 5 6 7 8 9 10 |
Sub Gethlinks() Dim shp As Shape For Each shp In Sheet1.Shapes shp.TopLeftCell.Offset(0, 1).Value = shp.Hyperlink.Address shp.Delete Next shp End Sub |
It writes the hyperlink address one cell over then deletes the shape.
To fix the blanks, I selected all of the blank cells in column B, entered =B5, and committed the formula with Control+Enter. That copied all of the cells above the blanks.
Next I sorted on Column A and deleted any row that wasn’t a song.
I split all the song information on spaces so I could extract the city name.
That was a bust, so I just typed the city and state next to each song. I deleted duplicates and the two songs that didn’t really contain city names.
I downloaded the spreadsheet template from batchgeo and pasted my data into it
It processed all of the entries and I got this map
“I downloaded the spreadsheet template from batchgeo and pasted my data into it”
Thanks for this. It solved a problem that’s been bugging me for 12 months, unrelated to Excel/VBA but I’m so glad to have it solved!
There seems to be some differences in how browsers display the info in the pop-ups. In Chrome and IE, I get a clickable link, but it doesn’t work. In Firefox, I get no clickable link.
All three browsers show the video URL in the pop-up, but it has to be copied and pasted to be of any use.
I get the same in Firefox John. I thought that’s just the way it was. I wonder if I could have done something different.
For a moment I thought I’d finally found a way to create a map in Excel. I’ve been trying for several months to — forget geocoding — just map latitude and longitude into a Excel figure, preferably a chart but any kind of object would do to get started.