In keeping to a previous thread I mentioned that I’d post a thread on creating a mapquest hyperlink via vba on the board so here goes.
The code I used is below and it works great but it’s set for the United States. I’ll explain in detail how to create for other countries below…
Sub Map_It() Dim CoRow, MySheet Set MySheet = Workbooks("Table.xls").Worksheets("Customer") CoRow = MySheet.Columns("A").Find(ActiveSheet.Range("Co_Name").Value, MatchCase:=True).Row ActiveWorkbook.FollowHyperlink Address:="http://www.mapquest.com/maps/map.adp?searchtype=address&country=US&addtohistory=&searchtab=" & _ "home&address=" & Replace(MySheet.Cells(CoRow, 2), " ", "+") & _ "&city=" & Replace(MySheet.Cells(CoRow, 3), " ", "+") & _ "&state=" & Replace(MySheet.Cells(CoRow, 4), " ", "+") & _ "&zipcode=" & Replace(Format(MySheet.Cells(CoRow, 5), "0####", " ", "+"), NewWindow:=True Set MySheet = Nothing End Sub
To help gain understanding of what was done. I started by doing a search in mapquest for a specific address. Once the map was produced I copied the address line as it appeared in my browser, it looked like…
Now, what I noticed was for each variable (address, city, state, etc.) the information that I had entered was listed. Yet all the spaces had been replaced with + signs.
So, I created the above code yet referencing/looking up in a database the value for each variable and pulled them into the hyperlink while at the same time replacing the spaces with + signs. Then invoked to appear in a separate window.
It does appear that this can be done for any country/address. However, one would have to search the site first to see what the address line looks like. i.e
In the above example, I notice that the only variables are the City (Budapest). That may be true from all of Europe. If that is the case then the variables would be Country and City.
Anyway, I thought this was way cool. Next I’m going to work on gaining weather information and or travel information based on an address. If anyone is interested in that let me know and I’ll post the results.