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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.