Creating in Excel a hyperlink to a route made in Google maps

whats the address? So somebody can test it out

that is something for sure.

I'll add these pics to show what you are referring to"

2-7-2016 5-08-18 AM.jpg2-7-2016 5-09-31 AM.jpg

I get the same. Interesting

It all has to do with the interpretation of commas in Excel.
If you replace every comma in the internetaddress into a space, the result will be very similar to the result in Word.

Alternativer interpratation: the Irish beermarket is protected against Belgian 'spies'. Google seems to be paid for that..

I found the pasted link was changed in word.
I corrected this by (in word) selecting the hyperlink ... edit hyperlink ... and repasting the link into Address. This worked.

I believe the OP wants it to be like word hyperlink.

By the way changing the commas to spaces in the excel hyperlink, does make it more lacal, but its path is different than the word version.

2-7-2016 5-55-28 AM.jpg

From word I get Jamiestown
From Excel I get Ireland

After looking at the pasted hyperling the excel one has been truncated. Is there a maximum length of hyperlink in excel?

Word :https://www.google.be /maps/dir/Charleville+Lodge+Hotel+Hotel,+North+Circular+Road,+Phibsborough,+Ierland/St+Patrick's+Cathedral,+Saint+Patrick's+Close,+Ierland/Christ+Church+Cathedral,+Christchurch+Place,+Dublin,+Ierland/Guinness+Storehouse,+Ierland/Old+Jameson+Distillery,+Bow+Street,+Ierland/Dublin+Castle,+Dame+Street,+Ierland/The+Temple+Bar+Company,+Fourth+Floor,+Dollard+House,+2+-+5+Wellington+Quay,+Temple+Bar,+Co.+Dublin,+Ierland/@53.3491284,-6.284857,15z/data=!3m1!4b1!4m44!4m43!1m5!1m1!1s0x48670dd394bd4c0b:0xfab8c8e98a60557a!2m2!1d-6.279834!2d53.3591528!1m5!1m1!1s0x4844a7096c3558a5:0x92c8e69bb625b154!2m2!1d-6.2714767!2d53.3395154!1m5!1m1!1s0x48670c264b069187:0x454aaf4ec5991449!2m2!1d-6.271013!2d53.3434516!1m5!1m1!1s0x48670e8440c5056b:0xb31933927505e7a2!2m2!1d-6.2867093!2d53.341874!1m5!1m1!1s0x48670c2ec3a240b3:0x4648dc6ec655a14!2m2!1d-6.2773544!2d53.3483761!1m5!1m1!1s0x48670e873566ff89:0x50fcdf6c0e15686!2m2!1d-6.2674284!2d53.3428861!1m5!1m1!1s0x48670c282dc904e1:0x2119a1fe51f69ee6!2m2!1d-6.26547!2d53.3453285!3e2

Excel:https://www.google.be
/maps/dir/Charleville+Lodge+Hotel+Hotel,+North+Circular+Road,+Phibsborough,+Ierland/St+Patrick's+Cathedral,+Saint+Patrick's+Close,+Ierland/Christ+Church+Cathedral,+Christchurch+Place,+Dublin,+Ierland/Guinness+Storehouse,+Ierland/Old+J[/url]

Originally Posted by pgyselin

..If.........
....is pasted in the web browser or in Word or in an Excel cell as such then "Enter"or "Ctl Enter" or clicking on that cell results in opening Google maps and the correct route

If the same URL is entered in a cell using the "Insert Hyperlink" pop up window (obtained by pressing the right hand button of the mouse and clicking "hyperlink") and pasting the URL in the address line then clicking on the cell opens Google Maps but the last couple of points are lost and the route is no longer "Walking" but changed to "Car" instead.....

My Excel ( German ) does not give me the option ".....pressing the right hand button of the mouse and clicking "hyperlink"" ......" - The hyperlink option, which is often there, is missing in this case !?

I have often found that using
CTRL V
or
Right mouse click + Insert
gives in many situations different results.
More experienced people have often told me that trying to figure out which version of the Clipboerd may be used leads to madness....."

Alan

tony h found the issue I believe,

The complete hyperlink address does not show up on the excel hyperlink

But I did a test

I Pasted the Hyperlink into A1 formula bar,

PasteHyperlink.jpg

Then I use this code to create a hyperlink into A2

Please Login or Register  to view this content.

Now I have a hyperlink in A2

YayHic.jpg

Once Clicked, it took me to the correct map.

2-7-2016 6-44-12 AM.jpg

If you enter hypelinkaddresses in Excel the maxlength = 255 characters.

Originally Posted by snb

If you enter hypelinkaddresses in Excel the maxlength = 255 characters.

but as davesexecl showed Excel will allow longer addresses created through VBA

@ davesexcel
could you paste that text for me from your Cell A1 as text in a post, or in a Code window so that i can copy it
Thanks
Alan ( Hic..... )

Bill Jelen has suggested to use,
TinyURL

This way you can fit it into the hyperlink.

I did it and it works great.

PasteCode.jpg
2-7-2016 7-56-22 AM.jpg

New Hyperlink Address

http://tinyurl.com/heh4r9t

Originally Posted by Doc.AElstein

@ davesexcel
could you paste that text for me from your Cell A1 as text in a post, or in a Code window so that i can copy it
Thanks
Alan ( Hic..... )

Hi Doc,
It's just a copy of the OP's hyperlink

Please Login or Register  to view this content.

Originally Posted by davesexcel

Hi Doc,
It's just a copy of the OP's hyperlink.........

Thanks davesexcel

To officially answer the question:

Word hyperlink can handle all 1030 characters in the address, but excel can't and requires some work around.

Refer to post 11, that is what Tony is referring to, I even supplied pictures

what is a "Hyperlink Pop Up"?

Dave's solution didn't work for me - I wonder if I'm missing a reference?

Here's a short clip.

https://youtu.be/ex8UXTBL2k0

I still get application defined or object defined error

I got it!

Please Login or Register  to view this content.

*maybe it's just an idiosyncrasy of my system

That is interesting, using your code I get this error ....
For both xl'10 & xl'16
It also error for xl'03

2-7-2016 1-14-35 PM.jpg

Yeah, I'm getting the same error - I was so pleased when it put "Booze Me" into A2 that I thought it was OK

I'm back to wondering what the reference is

BTW - The TinyUrl works

Originally Posted by xladept

Yeah, I'm getting the same error -.....
BTW - The TinyUrl works

Okay Just to clarify,
-The code I provided does not work for you, but it works for me.

-Your code that works for you, does not work for me.

It does not matter what code works if you use the TinyURL, because the Hyperlink will work anyway.

Originally Posted by davesexcel

That is interesting, using your code I get this error ....
For both xl'10 & xl'16
It also error for xl'03.....

Just to complete the XL versions... In my XL2007 davesexel's code works. xladept's does not. The error when I click on the shown "Booze Me" ( translated from German is along the lines of "Reference invalid”, so i guess the same youS is getting )

I tried to google and understand what the SubAddress:= argument is about so as to investigate further, but I could not make any sense of it. !!!

Alan

Please Login or Register  to view this content.

Using Excel 2007

-	A
1	https://www.google.be/maps/dir/Charleville+Lodge+Hotel+Hotel,+North+Circular+Road,+Phibsborough,+Ierland/St+Patrick's+Cathedral,+Saint+Patrick's+Close,+Ierland/Christ+Church+Cathedral,+Christchurch+Place,+Dublin,+Ierland/Guinness+Storehouse,+Ierland/Old+Jameson+Distillery,+Bow+Street,+Ierland/Dublin+Castle,+Dame+Street,+Ierland/The+Temple+Bar+Company,+Fourth+Floor,+Dollard+House,+2+-+5+Wellington+Quay,+Temple+Bar,+Co.+Dublin,+Ierland/@53.3491284,-6.284857,15z/data=!3m1!4b1!4m44!4m43!1m5!1m1!1s0x48670dd394bd4c0b:0xfab8c8e98a60557a!2m2!1d-6.279834!2d53.3591528!1m5!1m1!1s0x4844a7096c3558a5:0x92c8e69bb625b154!2m2!1d-6.2714767!2d53.3395154!1m5!1m1!1s0x48670c264b069187:0x454aaf4ec5991449!2m2!1d-6.271013!2d53.3434516!1m5!1m1!1s0x48670e8440c5056b:0xb31933927505e7a2!2m2!1d-6.2867093!2d53.341874!1m5!1m1!1s0x48670c2ec3a240b3:0x4648dc6ec655a14!2m2!1d-6.2773544!2d53.3483761!1m5!1m1!1s0x48670e873566ff89:0x50fcdf6c0e15686!2m2!1d-6.2674284!2d53.3428861!1m5!1m1!1s0x48670c282dc904e1:0x2119a1fe51f69ee6!2m2!1d-6.26547!2d53.3453285!3e2
2	Booze Me

FoodsLookUpTable

Originally Posted by Doc.AElstein

I tried to google and understand what the SubAddress:= argument is about so as to investigate further, but I could not make any sense of it. !!!

Alan

The sub address is used, for example, to provide a reference to a section within a document. So the original hyperlink maybe to a powerpoint and the subaddress the slide number.

Hi Tony H

Originally Posted by tony h

The sub address is used, for example, to provide a reference to a section within a document. So the original hyperlink maybe to a PowerPoint and the sub address the slide number.

I had found similar descriptions. That makes a certain sense. In a general wordy sort of way,. But I could not find, or by experimenting get a working example, to actually see what it was about. I have no idea about Power Point. So that one is lost on me

I guess there is something subtle to do with the definition and syntax of Addresses and Sub Addresses that I have not hit on yet.

I did just by chance hit a code below which the __SubAddress:= __ argument.... Consider the link to your reply, which ends up in the URL window at the top of my browser looking like this
http://www.excelforum.com/excel-gene...ml#post4311454

HTML Code: 

(_..... Note in passing that get that after i click on the link which i received per Email notification of your reply which is a bit different and I think takes me to a “New Reply”
http://www.excelforum.com/excel-gene...-new-post.html

HTML Code: 

_...... )

I found that the first part of this code below ( Rem 1) ) will work to get it. ( Here I have split the full Address using the __#__ as delimiter. Then each split part is in one the arguments, either __Address:= __or __SubAddress:=__ )

Please Login or Register  to view this content.

And note it will only work if I miss out the # ( The Full URL Link will not work if you miss the # out. If you have too many # it still "works" but goes to the Main Address )

‘_..................
The second part of the code ( Rem 2 ) ) is an example of how I had been trying to help xladept where he was using an Address of a string of zero length
Address:=""
And then the Sub Address was the full Address. It was not working for him, It also did not work for me. Initially i just thought that as__ Address:= __was a required argument, then it could not work.

Maybe the “taking” on idea could have something to do with that and i do not understand the logic or syntax to get it to work. I have seen by Googling that many people use__ Address:=""__ and then have a __SubAddress:= __argument. But again I could make any sense of what they were doing or get their codes to work. There must be some subtle thing to do with how you “tack” on the Sub address to the Main address.

So I am still mystified on this one: I guess i need an explained working example using the __Address:="" __along with a given__ SubAddress:= __argument

But thanks anyway for the Reply. Appreciate it. It encouraged me to look again and i discovered the # separator bit...
I guess there is some subtle way beyond my understanding that you use the SubAddress argument to get more efficiently at “Sub Things” within a main “Thing” . The following attempt by me to give a couple of Links to different posts in the Thread does not seem very useful or efficient – you can just as well loop on the Full Address. Just the code maybe looks a bit Tidier... At the end of the day I just need a well explained working example which seems to be missing in the Literature....

Please Login or Register  to view this content.

_. It would sound that the key to doing this “Sub Thing” efficiently is to have a “main Thing” there already. And then just “Add” consequently the “Sub thing”. But i struggle to see any way to do that.... i guess again there is something very subtle to the __ Address:="" __ ideas. ?!

Thanks again
Alan

Doc.AElstein subaddress
Staying with Excel, if you create a hyper link in one book to another (here Holiday.xlsx is my other book) then put a range address in the subaddress clicking on the hyperlink will open that sheet and select those cells.

The same can be achieved by using the Insert Hyperlink on the sheet and putting#address after the workbook name eg changing the address to D:\ExcelForum\Holiday.xlsx#data!B2:C3

Please Login or Register  to view this content.

Hi Tony H

Originally Posted by tony h

......
Staying with Excel, if you create a hyper link in one book to another......

Thanks again for common back here with that info..
Seems maybe the problem is in my understanding. That comes from that I had a 20 year “coma” as far as computers was concerned up until a couple of years ago. So I miss some obvious points. I thought a “Hyperlink" was a URL, so just to do with internet stuff.
I see now it is just a general thing that means “click me and I will do the rest, getting, setting, opening displaying in front of you what you want to see, or what program, and or document, or Web Side you want to have displaced in front of you...etc...”

So I guess The thing here is that VBA does a sort of guessing thing.. Takes the “Main thing”, or maybe better said “Main Object” to be what is given in the __ Address:=__ , then it has a whole lot of guessing ( or maybe RegEx patterning or whatever ) based on the “Type “ in __ Address:=__ , to look for or identify a sort of “Under Object” in the __SubAddress:=__.
In parallel it has a few things it checks for specific “Main Objects” like in the case of a URL it simply adds a # between the two things. Similarly it uses the # conventionally to distinguish between the “Main” and “Sub” thing if that appears in the

In your example, VBA “recognises” a File, and then has an instruction in it to select a Range, should one be given in the __SubAddress:=__. __SubAddress:=__.

The # is maybe a general thing in Hyperlinks that “joins” two “paths” for want of a better description.

Still begs the questions
_a) why bother with a __Address:= __along with a __ SubAddress:= __. Why not just use a # , to stick everything together and be done with it. ( That could be a throw-back to some earlier way of “routing” things, maybe .)
_b) still not sure how the __ Address:="" __ thing should work. Maybe something subtle to do with coercing the __SubAddress:=__ to work on a “pseudo” Active Hyperlink.

.. But i have learnt a bit about the Hyperlink again :-)
Thanks again.
Alan

P.s.

Here a code to.... “get a that sheet and cell displayed by clicking on from a this one”.... This code does what you suggest and showed me in your last code. In this example it puts a hyperlink in the current selected cell. This hyperlink when you click it takes you to the first cell in the first Worksheet ( First Worksheet item, item( 1) , that is to say the first tab looking from the left ) of the File specified by the Full File path and File Name ( including extension ( bit after the dot ) ) given in code line Number 35. ( This name must be changed appropriately to suit any particular file of interest ) . I still am getting nowhere with the __Address:="" code version, or in understanding any advantage of using the __Address:="" __along with a __ SubAddress:= __ version ( Code line 150 ) compared with the use a # , to stick everything together and be done with it way ( Code Line 200 )

Please Login or Register  to view this content.

I believe we have gotten carried away from the OP's original question, we lost him about 14 posts ago

@ pgyselin
Hi pgyselin

Thanks very much for the feedback.
Glad you have a solution that you are happy with it. I think that is the main thing we all want for you
Indeed if you are happy with that and understand it then that is a very good and neat solution for you.
I probably did get a bit carried away, as I do
I do actually have another very simple solution, that I think you would understand and should work for you.
I have no access to my Excel computer for a while.
I will post the solution, just as a last contribution to the Thread when I can.
But you stick with what you are happy with.
I think we all agree with that.
And thanks again for the feedback. That is always very welcome

Alan

@ Patrick ( pgyselin )
Hi Patrick ( or anyone else popping by )

See if you can understand this one. No worries if you do not, or are already overwhelmed with Info!!I I am using this elsewhere , so the effort is not gone to waste. In this particular adaption it is, as with all my codes in this Thread, just a variation of davesexel’s original Workaround solution for putting a long URL Link Thing ( greater than 255 characters ) into a cell and getting VBA to accept it as a URL and change it to a Hyperlink. BTW, I still keep coming back to that very concise and clear solution from Post #11 in this Thread.
http://www.excelforum.com/excel-gene...ml#post4311156


To make it as easy as possible I have used a particular type of VBA Code called something along the lines of a Worksheet change or “Event” code.. It differs from many other types of code in that it does things automatically. In this case it springs into life when you put your very long Thing in a Cell. I appreciate you know very little about VBA , but should you choose to have a go, it is worth trying to understand it a bit so here is the explanation of the code in plain English. ( And i give you a File at the end with the working code in it, so you just type your Long Thing in that File and you get the Hyperlink you want. )

Here is the very short simple code, ( which would be even simpler if i had not messed it up with my explain ‘Green comments in it !!!)

Please Login or Register  to view this content.

Here is the explanation of it

Line 10 is just a Variable for VBA to “hold” the full long Thing you type or Paste in ( your very long URL ) ( your "Long Thing" )
Line 20 “puts” your typed in thing in the Variable – It gets it from the Value in Target. Target is a reserved VBA Word which represents the Range ( a Cell in your case ) where you typed something in.

Lines 30 and 40 are talking about an aptly named variable, Where I Want To Insert My Long Thong, which for no particular reason I have chosen as the cells E1 through to E5. You can probably guess what you need to do to change that desired Input Range.
( Line 41, which is not used as i “ ‘commented it” out, would be the alternative to make the code work on the entire sheet. So Take out the First ‘ on Line 41 if you want to try that.).
Note if you only were interested in putting you Long Thing in the first cell, then you could change the bit in Line 40 to be __A1:A1__ or just __A1__

Line 50 I try to put in a form of plain English:__ Intersect(Range1, Range2)__is a VBA Method which give you the Range where the Ranges within the brackets (_____) cross or “Intercept”. If there is no cross it gives “Nothing” as an answer. So code line 50 says If it is NOT the case that Nothing is returned, Then it does Line 60
( I think if you think about that, you will see it is just a complicated way of saying ..“do something if you typed something in the Range you specified in Line 40 ( or 41 if you choose that option )..”...

Line 60 is basically “davesexuel’s Workaround” but applied to this sort of a code. In English it Puts the Hyperlink you want in the cell which was Target, ( where you typed your long Thing In ):
Anchor:= _____ says where The Hyperlink is written in
Address:=_____in this case is the URL itself
ScreenTip:=____is the bit of text in the cell which you usually put in as something like “Click Me”. But just for fun I used the whole text of your URL again
TextToDisplay:=___Is where you put what you want to be displayed in the Box which comes up when you hover over your Hyperlink cell with the mouse. Again just for fun I used your whole URL. I guess you might want just something Like “This goes to my Google Maps Pub Crawl Route when I click on it”
( A more technical explanation here, for my practice: The Hyperlink Property of a Range Object returns ( or refers ) to a collection of “all to do with“ the Hyperlinks associated with that Range Object. Amongst other things, a .Add Method is available with 2 compulsory and 2 optional arguments. Based on the arguments, a specific Hyperlink is put in the Range )

Lines 70 and 75 you do not need. That is where the code goes usually if you typed anything in outside the Range you defined in Line 40 ( or 41 ). Originally I did a code bit to tell you with a pop up box that you had typed somewhere outside your intended Range for the Long Things. But that was a pain when it came up every time I did anything anywhere _ Else_ in the Worksheet. So I “ ‘commented it” out. So Take out the First ‘ on those two lines if you want to try that.

This is what you would need to do to put the code in an existing Excel File:
As this code works on a Worksheet it must be put in a Worksheet Code Module, Not in the more usually used Normal Code Module.
One way to put that in is:
_(i) Copy the entire code from the Post Code Window to the Clipboard ( Select it all and hit Ctrl C )
_(ii) Click Right with the mouse on the Tab of your sheet of interest, then select something like “Show Code”
_(iii) The VB Editor should come up with a large empty code Window. Paste the entire code in that Window
_(iv) Hit Alt + F11 to get out of the VB Editor
_(v) Save and close the File ( but make sure you save as a “with macros” type __ .xlsm )
_(vi) Open the file and select something like “enable macros” when a warning comes up.

But you do not need to do that if you use this File, as i have already done that.
Here is the File, ( and it is also in the attachment to this post: ( XL 2007 “DaveLongSExuelHypeHiJack.xlsm” )
https://app.box.com/s/x8tg7r4k2p48u1ivvowzeydiaop0gtni


So basically it should “work” such that when you Paste your Long URL in the Specified cell Range it should automatically be converted to a Hyperlink, just as Excel would usually do if your URL had up to a 255 character length.

Alan

P.s. I also put the LEN(__) formula for you in a few cells in column F

P.P.s. There are all the other codes from this Thread in it and a few others, but not particularly well organised so you probably just want to ignore those for now.

Hi davesexcel,

Originally Posted by davesexcel

I believe we have gotten carried away from the OP's original question...

You originally solved the problem very efficiently. But i think the further follow ups are relevant and worthwhile. Enhancing and making the Thread very interesting and a great learning experience for anyone popping in. I do not think I am hijacking here. ... But I will PM you on that, so as not to really go off at a tangent in the Thread!!! ( or maybe a Water Cooler or Suggestions for Improvement Sub Forum discussion would make a bit of light relief from the endless Reports of recent Forum Problems!! )

I think the problem stemming from a very long URL and your basic code solution is enhanced by the further discussions. And maybe, who knows, the Split with the # and or in combination with the two __Address:= __along with a __ SubAddress:= __ ... could lead to another work around, somehow, by “dividing the two up...?

But i will take the hint.... This will probably be my last post here... so just a very niave attempt to split that long URL into two..


_ In the code below,
_ Part 1 ( Rem 1) ) is just a repeat of your original solution with no __SubAddress:= __
_ Part 2 ( Rem 2) ) simply puts half the URL in __Address:= __ and the second half in __SubAddress:= __.
Part 2) does not “work” like the original ( Part 1) ) , but instead just seems to ignore the __SubAddress:= __ and simply works as if the whole URL was the first half. As expected really.. a simple splitting is not what it is about,..... as already discussed in the last half of the Thread....

I will leave it at that, but any other enlighten, particularly good explained examples of a code using both the compulsory argument __Address:= __ and the optional Argument __SubAddress:= __ and any other enlightenment to if it is possible to get any code to work when __Address:= __ = “” could still. IMVHO be a good enhancement to the Thread.


“Bloging off!”
Alan

Code:

Please Login or Register  to view this content.

P.s.

Originally Posted by snb

If you enter hypelinkaddresses in Excel the maxlength = 255 characters.

Originally Posted by snb

It all has to do with the interpretation of commas in Excel.
If you replace every comma in the internet address into a space, the result will be very similar to the result in Word.
Alternative interpretation: the Irish beer market is protected against Belgian 'spies'. Google seems to be paid for that..

Interesting, - as you suggested replacing the comers with a space and the URL still works ( for 255 max characters )
This works , that is to say Excel recognises it and changes it to a Hyperlink when I paste it in ( Word does not after I did the change ) ( Length is 241 )

Please Login or Register  to view this content.

... but for me ( Germany ) so does this work

Please Login or Register  to view this content.

_.. No one or no institution has problems getting at Beer or its ingredient’s here!!!