Excel VBA Hyperlink stripping # symbol from link & make link display in popup window

I am using the following code to generate a hyperlink, but for some reason it strips out the # symbol that is part of the hyperlink.

The hyperlink needs to look like this: https://app.goformz.com/#Template/edit?id=424e6988-7820-4c52-b43e-a472003c1001

The output of the hyperlink looks like this:
https://app.goformz.com/%23Template/edit?id=a6f4b216-152f-4b27-9c93-a443016ca244

Code looks like this:
' Create Hyperlinks
Dim i1 As Integer
Dim s1 As String
Dim s2 As String

For i1 = 5 To Sheets("Templates").Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Row
    s1 = "https://app.goformz.com/" & s2 & "Template/edit?id=" & Trim(Range("A" & CStr(i1)).Text)
    s2 = ChrW(35)
    strTextToDisplay = IIf(s1 = "", "", "Edit Template")
    ActiveCell.Hyperlinks.Add Sheets("Templates").Range("D" & CStr(i1)), s1, , , strTextToDisplay
Next i1

Open in new window


Furthermore how can i make the link open in a popup window and define the size of the popup window ?
LVL 3
TroyIT Support AdministratorAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
can you post a sample excel so we can test the code...
0
 
HainKurtSr. System AnalystCommented:
try this

' Create Hyperlinks
Dim i1 As Integer
Dim s1 As String
Dim s2 As String

s2 = ChrW(35)

For i1 = 5 To Sheets("Templates").Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Row
    s1 = "https://app.goformz.com/" & s2 & "Template/edit?id=" & Trim(Range("A" & CStr(i1)).Text)
    strTextToDisplay = IIf(s1 = "", "", "Edit Template")
    ActiveCell.Hyperlinks.Add Sheets("Templates").Range("D" & CStr(i1)), s1, , , strTextToDisplay
Next i1

Open in new window


I moved s2 line outside loop...
you were using it before setting...
0
 
HainKurtSr. System AnalystCommented:
using this as url

javascript:window.open('http://www.google.ca','excPopUp','width=400,height=300,resizable=yes');

Open in new window


may work, or may give security warnings and may not work...
worth to try...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
TroyIT Support AdministratorAuthor Commented:
Hi HainKurt,

The hash symbol is not getting stripped anymore, but when i click on the link it goes to  here (www.)

https://www.goformz.com/#Template/edit?id=3be40c05-9fa3-48a3-bf66-a472003bee6b

instead of here (app.):

https://app.goformz.com/#Template/edit?id=3be40c05-9fa3-48a3-bf66-a472003bee6b
0
 
TroyIT Support AdministratorAuthor Commented:
The funny thing is when i edit the hyperlink it looks right

https://app.goformz.com/#Template/edit?id=24714c9c-4e4e-45e0-810d-a45b00806ada
0
 
HainKurtSr. System AnalystCommented:
I used the same url and opened the web page

https://app.goformz.com/#Template/edit?id=24714c9c-4e4e-45e0-810d-a45b00806ada

Open in new window

and no issues with # at all
29062281.xlsm
0
 
TroyIT Support AdministratorAuthor Commented:
I have tried it on 3x different computers running the same excel 2013 version and they all take me to the wrong link.

What excel version are you testing it on.
0
 
HainKurtSr. System AnalystCommented:
MS Office 2016 Plus :)
0
 
HainKurtSr. System AnalystCommented:
try this link

https%3A%2F%2Fapp.goformz.com%2F%23Template%2Fedit%3Fid%3D24714c9c-4e4e-45e0-810d-a45b00806ada

Open in new window


got it from
https://www.w3schools.com/tags/ref_urlencode.asp
0
 
TroyIT Support AdministratorAuthor Commented:
where am i testing this as it doesnt appear to be a valid link
0
 
HainKurtSr. System AnalystCommented:
in excel, or when creating your link
0
 
TroyIT Support AdministratorAuthor Commented:
I have just edited the hyperlink in your sample and it still takes me to the www. page
0
 
HainKurtSr. System AnalystCommented:
I have just edited the hyperlink in your sample and it still takes me to the www. page

what is www page?
try these links
29062281.xlsm
0
 
TroyIT Support AdministratorAuthor Commented:
here is the file.
29062281.xlsm
0
 
TroyIT Support AdministratorAuthor Commented:
0
 
HainKurtSr. System AnalystCommented:
ok, looks like there is a bug with excel and angular site I guess with # key...

solution will be, to create a web page, say

www.mydomain.com/redir.php

now you link to

http://www.mydomain.com/redir.php?url=encoded_url_here

Open in new window


ie

http://www.mydomain.com/redir.php?url=https%3A%2F%2Fapp.goformz.com%2F%23Template%2Fedit%3Fid%3D24714c9c-4e4e-45e0-810d-a45b00806ada

Open in new window


then in this page, you will have this:

<script type="text/javascript">
        function getURL(key) {
            key = key.substr(5)
            key = key.replace("%23", "#")
            return key;
        }
        function redirectURL(newPath) {
            var userAgent;
            userAgent = navigator.userAgent
            if (userAgent.indexOf("Protocol Discovery") < 0) {
                window.location = newPath
            }
        }
        redirectURL(window.location.protocol + "//" + window.location.host + "/" + getURL(location.search))
    </script>

Open in new window


Hyperlink containing # in Excel
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c2860611-ef3d-4880-b168-253c1d643443/hyperlink-containing-in-excel?forum=exceldev

and now you should be ok :)
0
All Courses

From novice to tech pro — start learning today.