If you are planning your trip with Microsoft Excel, besides those stuff from Excel, for example, spending, charting, tabular data from one location to another location, etc, there is good news for you. Let's learn how to add more details like map, photo, distance, and time, to every location we plan to visit. In the meanwhile, let's also learn to code with API and JSON.

Step 1: There Are Few Things We Need

We need the following for it to work:

Sign up for an account from the map service provider, for example, MapqQuest, Google Maps, Bing Maps, etc.
Microsoft Excel Macro file, which we need to code it to access the map service API.
Obtain photos and descriptions on the internet. Of course, not encourage for copyrighted photos.
Continue to code in Excel for some drawings to display the map, photos, and description for every location.

Step 2: Sign Up for the Map Service

After some explorations, I decided to use the free service from MapQuest - https://www.mapquestapi.com/ for the demo purpose. And the API I'm using are two:

The static map for two locations- https://developer.mapquest.com/documentation/stat...
The route matrix for two locations - https://developer.mapquest.com/documentation/stat...

Like any other API call, we will need to request the API key, which provided after the account is approved. The API key will need to include in the URL, as well as the code when making the request to MapQuest. Like any other free service, there are always limitations like the maximum call. But, it is enough for our purpose.

For the code on the API key, I make it real simple, where you just need to replace the third line from the attached source code.

Note: The mode we are using in for the map is the "driving", not flight or train or another mode. It is as expected if we see the first row within 2 countries return with no distance or time information.

Step 3: Microsoft Excel Macro File

This is to learn the code with Visual Basic Application (VBA) with Microsoft Excel. The API call is not that hard with VBA. The harder is the drawing with Microsoft Excel shape object, to load the map and photo, adding text to it, using an algorithm to calculate the width and height of the rectangle (it's like a card), to place it to below or to the right. It is in the code, feel free to check it out.

API calls in VBA are using the MS XML object, Microsoft Scripting Runtime, and VBA-JSON.

For the first two, we must add a reference from the Visual Basic Editor from the menu bar "Tools | Reference". Please refer to the screenshots in this step. Or we can refer to the "Help" worksheet if already downloaded this project from Instructables.

For VBA-JSON, we will need to get it from GitHub - https://github.com/VBA-tools/VBA-JSON. Download and save it locally, then import it to the VBA modules.

Code to make the API call:

Dim xmlhttp As New MSXML2.XMLHTTP60<br>Dim xmlresponse As New DOMDocument60

xmlhttp.Open "GET", urlAPIMapUrl, False<br>xmlhttp.setRequestHeader "Content-Type", "application/json"<br>xmlhttp.send

Code to parse the JSON:

Set objJason = JsonConverter.ParseJson(xmlhttp.responseText)
For Each objJasonItem In objJason.Keys
	If LCase(Trim(objJasonItem)) = "distance" Then
            strDistance = Replace(JsonConverter.ConvertToJson(objJason(objJasonItem)), """", "")
        End If
        If LCase(Trim(objJasonItem)) = "time" Then
            strTime = Replace(JsonConverter.ConvertToJson(objJason(objJasonItem)), """", "")
        End If
Next

Note: The "Help" worksheet contains some of the useful information to help you to get familiar with the coding, besides call up the "Developer" tab, the VBA editor, the object reference, import the VBA-JSON, I also provided "Tooltip" like to show how to save from Excel file to Excel macro file on Step 12. I supposed some are worried about virus/safety with Excel macro file. I provided with Excel, Excel macro file, and code (text file) in the download, as well as some picture files. The "Tooltip" is also a step-by-step briefing on how the data is setup. Remember to click on the orange cell, whenever you see it in the file.

Step 4: Obtain Photos and Descriptions From the Internet

There are lots of location photos on the internet, and one of the good sources is from Google Maps. For demo purposes, as well as copyrights concern, I'm using the photos from my Japan trip.

When you plan your trip, you may already have some photos, the story, or history of the places, location details, etc which is well fix here. We are learning to show the photos based on the location and the map, we code it the way that, it will show the photo/description for the exact location, as well as other visiting places in the same city. It will show the time and distance for each location to benefit for the visiting. For example, from Chiba to Tokyo, we will see Chiba to Shinjuku, Chiba to Narita, Chiba to Hachioji, with the distance and time. The purpose is to help us to determine what is our next location in the same city, from the near one to the far one. This will avoid us from going to the east of Tokyo then west of Tokyo, then back to the east again.

Note: For the photos and description (Wiki? travel website?) of each visiting place, we are still required to manual edit in Excel. This is why I mentioned well fix here earlier on if you already have the information. By the way, if you are adding another API to Wiki and Google Maps Photo, please help to share it to benefit others as well.

Step 5: Continue to Code in Excel for the Drawing With All the Details

It may confuse to explain the code on how the drawing work, hence I put it in "English", to explain it.

Upon the change of the location selection on column "Arrive", the Excel will trigger the below:

Take the value from the "Depart" and "Arrive" columns.
Access the map API by passing the source (Depart) and destination (Arrive) information, API returns the static map information about the map file, distance, and time, in JASON (or XML) format.
At the end of the Excel row, on column H, draw a rectangle and loaded the map file, access the JASON information, obtain the distance and time, then added on top of the map.
Add up the time from "Depart On" to "Arrive On" column.
Access your database, which is stored in the Tokyo worksheet, locate the location, draw a rectangle below the map, loaded with the location photo. Then draw another rectangle right below the location photo, loaded with the description or history of the location. This rectangle is half-height from the photo. Click on it will see longer details. Applying some formating to the rectangles.
Access the same worksheet, draw multiple rectangles with see-through effect, and loaded the photos and descriptions for all the locations with the same city.

By the way, we can select the location for "Tokyo" for it to display all the locations we have in the database/worksheet in Tokyo, then click on the location picture to select it for the locations we want to visit. We can achieve this by selecting it multiple times.

Step 6: Run the Code

Let me do it in a different way when we run the code.

To complete our entire Tokyo visit from Penang without resting and eating:

Depart from Penang at 1 pm, arrive at Narita airport. No time information due to the map is in driving mode. From now, we will rent a car at Narita airport.
Assume we don't know any visiting place in Tokyo, we select "Tokyo" on "Arrive" column. Cool, we have Senso-ji, Shinjuku, Hachioji, Kabukicho, and Narita. The description tells us what is interesting about the places. Wait! There is no picture for Senso-ji. Obviously, we missed that out! We are kind of interested in Shinjuku, click on the photo to select it. No, we don't want to select Senso-ji.
If we drive from Narita airport to Shinjuku for 89.5km, we take about 50 minutes on Sunday. Yes, time may change if traffic is bad.
And then with "Depart" column of Shinjuku, select "Tokyo" again in "Arrive" column. Now we select Kabukicho, why? Among others, this place is just a few minutes drive.
From Kabukicho in Tokyo, it will take x minutes for x km, for us to drive to Hachioji.
From Hachioji, drive back to Narita airport take us about 2 hours with 143km.

We just complete our visit to Tokyo for one day!

Happy coding!