Advertisements

An original point of view

GIS

Launching Google Maps from Excel with Chrome browser

Google Maps directions VBA Excel3

Pic. 10 The example of getting multiple directions with Google Maps and VBA Excel.

The work with addresses is quite often in our Excel spreadsheet. Today I would like to demonstrate to you how to parse the address string in the Excel cell in order to make it auto-populated on Google Maps or any other website.  Nowadays many websites feature the permalink,  which makes them more dynamic. The permalink is helpful in reaching the specific item on the websites. Because we are going to deal with the address the most important websites are map-based. The permalink in this kind of website can be based on address or coordinates.  If we take into account Google Maps, our today’s example, we can see how the permalink works(Pic. 1).

Google Maps permalinks

Pic. 1 Google Maps permalink elements: 1 – main address; 2 – coordinates; 3 – zoom factor (Google Maps & Terrain) or height above the ground (google satellite); 4 – a place populated by postcode; 5 – full address; 6 – directions; 7 – direction between places (from/to); 8 – search nearby; 9 – object type search. All of them distinguished by specified colors in order to better understanding.

As you can see, Google Maps have a dynamic permalink, depending on the operations, which can be done. This example roughly shows the importance of the permalink, which makes the website dynamic, as mentioned above. This situation applies to the other websites too.
Knowing how Google Maps permalink works, now we can devise some way how to grip it from MS Excel.
First of all, we can divide this URL string between the generic part and the dynamic part. The generic part is definitely the “http://google.com/maps/“, marked as the main web address (Pic. 1) which won’t change at all. The second part if behind the slash and it will change as we do some operations on the website.
So the second part of this link can be reliant on the string, which we prepared in our MS Excel document. This string is obviously the address, but it must be formatted correctly if we wish to use it along with the Google Maps permalink.
Before I show you how to parse the Excel address string let’s see how Google Chrome works with VBA Excel.
The most simple way to open any website in Google Chrome using VBA Excel is like in the code below or here.

Sub  Chromeweb()

Dim chromePath As String

chromePath =  “””C:\ProgramFiles(x86)\Google\Chrome\Application\Chrome.exe”””

Shell (chromePath & ” -url http:maps.google.com”)

End Sub

Launching this macro we can open the Google.com website. However, there are 2 basic things to mention, before you do the copy & paste of this code. The first one is the destination folder path for our Chrome application in Windows, which is mostly the same, although if you did the custom installation it is good to check it. The second one is the target website, which can be any other than Google (behind the -url just swap the maps.google.com link with yours).

Google Maps default view

Pic. 2 Default view of Google Maps launched by VBA Excel macro for Google Chrome.

The basic macro for launching Google Chrome address is not enough to make the full permalink management in Google Maps, what we need now. This code is not able to manage the dynamic website.
To make our VBA Excel macro more flexible regarding the dynamic websites, we must expand it.
The most reasonable way to launch various website permalinks in Chrome is using the Selenium software.
The Selenium is one of the web test tools,  which gives you much more functionality within the objects, that you can control.  These objects are i.e. websites, from where you can easily scrap the interesting content or simply automate them.  In our case, the second attitude is very important because we want to have our Google Maps permalink being flexible.
There are quite a few sources, where the appliance Selenium with the VBA Excel code has been explained in detail.  There are a few steps to do so. However, for parsing the Excel address in Google Maps or other small stuff on different websites we don’t really need the Selenium setup. This is the major goal of this article, to show you how can you make the Google Maps platform dynamic on the basis of the VBA Excel macro only. You will spend a bit less time to do this, getting pretty much the same effect.

Before we will get started it’s important to check the Google Maps permalink out again. As you can see above (Pic. 1), the permalink includes some strings linked by “+” or eventually the coma (not to mention the slashes typical for any web address).
Now, we must prepare our address string in Excel in order to match it with the Google  Maps permalink. Let me show some steps on how to do this with a single address.

Your example address in excel is:

4, Howburn Court, Aberdeen, AB11 6YA

based in the cell A1.

So you have effectively two options to show it in Google Maps. There is a third option available also, but I am leaving the geocoding issue for the next time.
The first option is extracting the postcode only and incorporate it into the Google Maps permalink.  The second way is to parsing our whole address. By the looks of it, that our example address in Excel has some fixed spaces, which usually occur.  These fixed spaces you can see as the commas or simply the spaces between the words.
In order to adjust our address string to the Google Maps permalink, we must do some necessary alterations of this string.
If we are going to extract the postcode only, the useful option will be extraction the last characters from the string. The number of characters will depend on our postcode description. In the UK the postcode comprises 2 single characters (AB11 6YA) separated by space, so we must extract 2 last characters from our string. In Poland for example the postcode contains one character (38-400), so only last character extraction will be required.
Staying with our example, we must extract the last two characters then. For this purpose we need formulas like this:

=TRIM(RIGHT(SUBSTITUTE(A1,” “,REPT(” “,60)),120))

or

=MID(A1,FIND(“@”,SUBSTITUTE(D18,” “,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))-1))+1,100)

which extracts the full UK postcode for us. When you replace my cell with yours, the result which you are getting should be the postcode only for this location:

AB11 6YA

In this event, the last step left. As you have noticed in the Google Maps permalink this is a “+” symbol, connecting the characters. There is an easy function in Excel, which can swap our space quickly with the “+” symbol. This is the SUBSTITUTE function.

=SUBSTITUTE(A2,” “,”+”)

Seeing this formula above, it’s easy to understand what is happening. We are filling up space with the “+” figure. Now our postcode string has been turned into one character.

AB11+6YA

Now our postcode in cell A3 should be ready to populate in Google Maps permalink. It would work, but there is some mismatch in this method, which causes a lack of response from the permalink. As a result, our default location is opened. I would suggest doing an additional parse of this postcode. First of all, we need to extract one element from our postcode. It must be the major part of our postcode – AB11. In this event we must use the following formula:

= LEFT(A2,FIND(” “,A2) -1)

As you may have noticed I am doing this operation on my original postcode, including space between two characters. The -1 means, that I must remove the space between AB11 and 6YA.

As a result, I should get:

AB11

Now, the very last step is to concatenate our piece of a postcode (cell A3) with the substituted one in cell A4, which includes the “+” symbol. It can be done quickly with the CONCATENATE function.

 =CONCATENATE(A3,”+”,A4)

We must input the “+” between these 2 cells, as we still need our string in one piece. Finally, we are getting, the postcode string in cell A5 as per below:

AB11+6YA+AB11

which is ready to be parsed in the Google Maps permalink.

That’s all with the pure Excel stuff. Now, we must use the proper VBA code to create the working macro.
I showed you above the very basic macro solution for launching the webpage straight from Excel. Now we must expand it, enabling us to take advantage of the dynamic webpage.

First of all, the two-part VBA code is needed, which includes the function.

Sub GoogleMapsChrome()
Dim Postcode As String
Dim url As String
Postcode = Sheets(“Sheet1”).Range(“A5”).Value
url = “https://www.google.com/maps/place/+” & Postcode
OpenChrome url
End Sub


Sub OpenChrome(url As String)
Dim Chrome As String
Chrome = “C:\Program Files (x86)\Google\Chrome\Application\Chrome.exe -url”
Shell (Chrome & ” ” & url)
End Sub

Now, let me explain the code above:
Firstly we need to declare our variables, which are the postcode and the web link.
Our postcode is based in the cell A5 including our final address string in Excel, which is now: AB11+6YA+AB11.
Our target URL path is Google Maps permalink, which is to be based on our address. If Google Maps permalink comprises basically 2 elements: a generic and dynamic one, we will determine the string of its second element.
As it was shown above (Pic. 1), the generic part of the Google Maps address is: http://Google.com/maps. The dynamic starts from the 2nd piece, which varies accordingly as our purpose of use changes. If we want to find the address-based place, then the generic part of the permalink will be: http://www.google.com/maps/place/.
Next, everything beyond this slash is expressed by “+” figure, appearing everywhere, where would be simply space.  Although the “+” symbol doesn’t appear straight after the slash nowhere in the address (Pic. 1), it remains necessary for further autoredirection done by Google Maps. Regarding this situation, we can add up the first “+” symbol straight beyond the last slash, making our fixed part of the link ready to use with the parsed Excel variable.  Our link will look as follows: http://www.google.com/maps/place/+ .
What is happening next? We are chaining this link with our prepared address string in the cell A5, which has been declared as a variable with the Dim statement.
The last line of code represents the function needed to open this link in Google Chrome. The function written underneath includes the basic command for launching the Chrome browser, as discussed previously.

Excel address parsed for Google Maps

Pic. 3 The Excel file with the address string parsing for Google Maps permalink purpose. The button with macro has been already added.

Above you can spot all the cells used for the purpose of this brief tutorial. The code showed above has been assigned to the button. Now once you click this button, Chrome should open the Google Maps under the given address (Pic. 4). Wait maybe 2-3 seconds and see how the platform deals with the redirection. Now the “+” symbol behind the slash is gone and new permalink elements become visible (Pic. 5).

Google Maps address opened

Pic. 4 Our postcode has been used as the part of Google Maps permalink. In the effect, the website is opening under the given address.

Google Maps Excel address redirected

Pic. 5 The permalink with Excel Address being redirected to the proper Google Maps Permalink including the information about the highlighted place.

This is a simple method to open Google Maps under our desired address. We used only the postcode. It will be valid for finding the place itself, although if you want to search nearby this place it’s better to parse a whole address.
For this purpose, we must do one basic step, which will change the comma & space with the “+” symbol for us.
The quickest way to write it down is the double SUBSTITUTE function. Let’s put our cell A7 this formula and see what happens.

=SUBSTITUTE(SUBSTITUTE(A1,”,”,””),” “,”+”)

We are replacing the comma with the vacuum, without space, which is marked as the “” symbol (nothing in the quotes) and the existing space with the “+” symbol at the same time. The existing space is always marked as “something” between the quotes: ” “.
As a result, we are getting a whole address string parsed for the Google Maps purpose.

4+Howburn+Ct+Aberdeen+AB11+6YA

If we supersede this address string with the postcode shown previously, the Google Maps should be opened under the same address, but in this case, the platform is more precise (Pic. 6).

Opening Google Maps from Excel

Pic. 6 The specified location opened in Google Maps as the effect of parse full address string in MS Excel.

Now it looks like we gained instant access to the Street View, which wasn’t so easily reachable previously (Pic. 5), apart from the yellow pegman, which is always present.
Like I said before, the Google Maps it’s not only the address finder. We can also search for places near our location.
In this situation, the small changes in our VBA code are required. We must change the Google Maps link, equipping it with the type of place, which we are looking for.

Sub GoogleNearby()
Dim Postcode As String
Dim urlB As String
Postcode = Sheets(“Sheet1”).Range(“A7”).Value

urlB = “https://www.google.com/maps/search/mcdonalds+near+” & Postcode

OpenChrome urlB
End Sub

Treat it code as another macro. We are using the OpenChrome function again. See, what had changed here. The Google maps permalink as the urlB variable now includes the “search” section with the Mcdonald parsed with the “+” symbol. After this string, we can input our address defined in the cell A7  by the existing VBA code.
The result will be nice. All the Mcdonalds will be populated near our defined place (Pic. 7).

Google Maps mcdonalds VBA Excel

Pic. 7 Mcdonalds nearest to our address populated by the “Search nearby” option in Google Maps straight from Excel.

The last thing to discuss in this article is the management of the direction. As we can easily open Google Maps under our address, we can get the relevant directions too. There are effectively 2 options to do so. The first option comes from the generic link  leading to our current location, which is described as below:

https://www.google.com/maps/dir/my+location/

and the second option, which requires to add a separate address, from where we want to get direction to our current location.
The first option requires only one address, which we are dealing with. Another one comes from our location. I am assuming, that you don’t need more addresses than 2. We will discuss later how to use a multitude of addresses in our direction.
In this the simplest event our VBA code will look as follows:

Sub Directions()
Dim location As String
Dim urlC As String

location = ThisWorkbook.Sheets(“Sheet1”).Range(“A7”)

urlC = “https://www.google.com/maps/dir/my+location/” & location

OpenChrome urlC
End Sub

Obviously, your OpenChrome function has been set earlier.
As a result, you should have the Google Maps opened with your directions. Take a look at how the permalink changes, where /my+location/ is superseded by the closest address corresponding to the current location of our device (Pic. 8).

Google Maps directions VBA Excel

Pic. 8 The example of getting directions with Google Maps and VBA Excel. The upper image shows the initial permalink, as comes from our code. The lower image shows the permalink after Google Maps correction with the current address of our device, where: 1 – the generic part of Google Maps permalink; 2 – address of our current location.

In the case of the second option considered, we must add another address, independent from our location. Imagine, that your new address is based in column A9 from now. This is a new variable, which we must define in our VBA code in order to make it running.
Let’s add this new variable to our existing code, which should look like this:

Sub Directions()
Dim location As String, location2 As String
Dim urlC As String, urlD As String

location = ThisWorkbook.Sheets(“Sheet1”).Range(“A7”)
location2 = ThisWorkbook.Sheets(“Sheet1”).Range(“A9”)

urlC = “https://www.google.com/maps/dir/my+location/” & location
urlD = “https://www.google.com/maps/dir/” & location & “/” & location2

OpenChrome urlC
OpenChrome urlD
End Sub

, where these 2 methods have been included.

Considering our new line of code, we must be aware of the order of our addresses. The first address is always our starting point, the second one is our destination. It’s easy to mix (Pic. 9).

oogle Maps directions VBA Excel2

Pic. 9 The example of getting directions with Google Maps and VBA Excel. Look at the address order: 1 – the starting location; 2 – the target location.

The last exercise to work with is adding the multiple addresses into our permalink. Everyone knows, that Google enables us to input at least several addresses on our way between the start and finish our journey. It can be done, by clicking the “+ Add destination” option. To give it a ride via the VBA code, we need at least one another address. We can add the new address to our cell A10 for instance.
Next, following the previous procedure, we need another variable defined in our code, which should look as follows:

Sub Directions()
Dim location As String, location2 As String, location3 As String
Dim urlE As String

location = ThisWorkbook.Sheets(“Sheet1”).Range(“A7”)
location2 = ThisWorkbook.Sheets(“Sheet1”).Range(“A9”)
location3 = ThisWorkbook.Sheets(“Sheet1”).Range(“A10”)

urlE = “https://www.google.com/maps/dir/” & location & “/” & location2 & “/” & location3

OpenChrome urlE
End Sub

, including only one URL this time.
In turn, our route has been expanded on this new address (Pic. 10).

Google Maps directions VBA Excel3

Pic. 10 The example of getting multiple directions with Google Maps and VBA Excel.

Now we have got three addresses populated in Google Maps, which show our entire route.
So we are done for now. Our final excel document should look like below (Pic. 11) and it’s downloadable here.  If you wish to go through this whole task again and get more practice, you can use this file.

Excel file for Google Maps

Pic. 11 The screenshot of my Excel file tailored for opening addresses in Google Maps.

Launching Google Maps from Excel I just wanted to show you the deal of VBA Excel macros with the websites and their permalinks.
Google Maps is only one example of using it. You can use this advice for other websites & map servers, depending on their permalinks. The issue will be similar. Make sure, that you are confident of the permalink of that given website before you start preparing variables in the spreadsheet and coding macros. Make sure, that there are no frill spaces, commas, etc. You should also double-check the order, in which all the permalink pieces are located. If everything is alright, then you definitely will be successful. Google Chrome browser doesn’t really need the Selenium software if all our elements are deliberately prepared at the Excel spreadsheet stage. Good luck!

Mariusz Krukar

 

Links:

  1. What is a permalink?
  2. https://www.makeuseof.com/tag/how-to-automate-firefox-or-chrome-with-vba-and-selenium/
  3. https://codingislove.com/browser-automation-in-excel-selenium/
  4. https://www.guru99.com/selenium-alternatives.html
  5. Using VBA and Selenium – user guide
  6. https://exceptionshub.com/open-google-chrome-from-vba-excel.html
  7. https://exceljet.net/formula/get-last-word
  8. https://www.extendoffice.com/excel/formulas/excel-get-last-word.html
  9. https://exceljet.net/formula/extract-last-two-words-from-cell
  10. https://www.pcworld.com/article/3179414/excel-functions-7-ways-to-use-text-functions.html
  11. https://excelmacromastery.com/vba-dim/#A_Quick_Guide_to_using_the_VBA_Dim_Statement
  12. https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/dim-statement

Forums:

  1. https://stackoverflow.com/questions/41817393/excel-vba-and-google-maps/61463355#61463355
  2. https://exceptionshub.com/open-google-chrome-from-vba-excel.html
  3. https://stackoverflow.com/questions/5915325/open-google-chrome-from-vba-excel?noredirect=1
  4. https://stackoverflow.com/questions/33058523/parse-webpage-through-vba-with-chrome#comment53938047_33058523
  5. https://www.excelforum.com/excel-programming-vba-macros/973083-how-do-i-navigate-to-a-website-using-google-chrome-instead-of-internet-explorer-in-vba.html
  6. https://stackoverflow.com/questions/53351800/how-to-open-chrome-with-extension-using-selenium-vba
  7. https://www.quora.com/Is-there-a-selenium-or-other-solution-written-in-VBA-for-Excel

My questions:

  1. https://stackoverflow.com/questions/60548271/open-multiple-urls-in-the-chrome-browser

Wiki:

  1. Permalink

Youtube:

 

 

 

 

Advertisements
Follow

Get the latest posts delivered to your mailbox:

%d bloggers like this: