Hyperlink a long URL of more than 255 characters
Hello All,
To get the drilldown feature we need to implement the hyperlink functionality to our excel report. we have tried the function Hyperlink ("URL","Friendly name"), but this functional supports only 255 characters. we have a URL containing 450+ characters. we are using the URL
"https://chl20022714.ch.hedani.net:9804/analytics/saw.dll?dashboard&PortalPath=/shared/ASR/_portal/Demo%20-%20URL%20drillthrough&Action=Navigate&col1=%22ASR.ASR_MV_A500_SUMMARY%22.BALANCE_PRD&val1=20129&op1=eq&col2=%22ASR.ASR_MV_A500_SUMMARY%22.REPUNIT_CD&val2=00877&op2=eq&col3=%22ASR.ASR_MV_A500_SUMMARY%22.ACNT_PSFT_NBR&val3=360621&op3=eq&col4=%22ASR.ASR_MV_A500_SUMMARY%22.GAAP_ID&val4=0000+0001+0"
We have tried the below options
1. Delivered Hyperlink function of excel (Doesn't solve our purpose, reads only 255 characters)
2. Concatination with the Hyperlink function:- some URL's are working fine but the URL given by us is not working. It throws the error as #value!
3. Right click on the cell then providing the URL and the URL name.(doesn't resolve our purpose as it reads only 255 characters)
4. Macro like
Sheets(1).Hyperlinks.Add Range("d2"), urladd, "", "URL Len: " & Len(urladd), "A Long URL"
we don't want to use the Marcos to acheive this task.
5. Got the below code from one of the user on ExcelKey as below
Function myHyperlink(cell As Range, _
hyperlinkAddress As String, _
Optional TextToDisplay As Variant, _
Optional ScreenTip As Variant)
Set cell = cell.Resize(1, 1)
If IsMissing(TextToDisplay) Then
TextToDisplay = hyperlinkAddress
End If
If IsMissing(ScreenTip) Then
ScreenTip = hyperlinkAddress & " - Click here to follow the hyperlink"
End If
cell.Hyperlinks.Add Anchor:=ActiveCell, _
Address:=hyperlinkAddress, _
SubAddress:="", _
ScreenTip:=ScreenTip, _
TextToDisplay:=TextToDisplay
' There doesn't seem to be another way to set TextToDisplay
myHyperlink = TextToDisplay
End Function
but this is also not working in Excel 2010.
can somebody please help us in acheiving our task in using the long URL as HYPERLINK ???????
Please don't suggest about http://tinyurl.com/ as we need a dynamic parameterized URL for reporting purpose.
PS: Please consider me a novice user of Excel while replying. Request you to please reply in detail including all the details.
Regards,
Jitendra
email : jkumar11603@hotmail.com
myHyperlink Function
How do you use that function you got there? I want to try to see if it works in Excel 2016. I can't seem to figure out the syntax. Thanks in advance.
Instead of defining hyperlink
Instead of defining hyperlink as string define as variant and try it again.
I hope it works.
Cátia Santos
gives error as #VALUE!
Thanks for response Catia.
Tried your idea too. It doesn't work.
Initially it have error as "Text values in formulae are limited to 255 characters. To create text values longer than 255 characters in a formulae, use CONCATENATE function or the concatenation operator &". I used "&" but the result is shown as "#VALUE!".
I'm calling the function from cell C15 as
=myHyperlink(A1,"https://chl20022714.ch.hedani.net:9804/analytics/saw.dll?dashboard&PortalPath=/shared/ASR/_portal/Demo%20-%20URL%20drillthrough&Action=Navigate&col1=%22"&"ASR.ASR_MV_A500_SUMMARY%22.BALANCE_PRD&val1=20129&op1=eq&col2=%22ASR.ASR_MV_A500_SUMMARY%22.REPUNIT_CD&val2=00877&op2=eq&col3=%22ASR.ASR_MV_A500_SUMMARY%22.ACNT_PSFT_NBR&val3=360621&op3=eq&col4=%22ASR.ASR_MV_A500_SUMMARY%22.GAAP_ID&val4=0000+0001+0","dispaly","text")
Jitendra
maybe wrong url
I know that i may sound stupid, but are you sure about the URL? because it does not work on internet.
The said URL works on Intranet and not on Internet
This URL is a link to our company internal portal. This works on intranet and not on internet.
you can try to take a very long url(more than 300 characteres) from internet and check if it works fine with you and let me know the method to resolve this.
Jitendra
macro that worked with 263 characters URL
Hello,
I got it, I did this macro and it worked with a 263 character URL ( the longest I found in internet to do the test).
Sub Longhyperlink()
ActiveWorkbook.FollowHyperlink("http://www.thelongestlistofthelongeststuffatthelongestdomainnameatlonglast.com/wearejustdoingthistobestupidnowsincethiscangoonforeverandeverandeverbutitstilllookskindaneatinthebrowsereventhoughitsabigwasteoftimeandenergyandhasnorealpointbutwehadtodoitanyways.html")
End Sub
Just copy it and replace for your url if it does not work it means that it is not because it is longest than 255.
Do not worry about macro, you can still make a button on your report and nobody will find out that indeed there is a macro behind a simple click ;)
Cátia Santos
The said URL works on Intranet and not on Internet
-
hyperlink
did it work?