Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?

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

Posted on 2017-10-12
16
Medium Priority
?
27 Views
Last Modified: 2017-10-16
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 ?
0
Comment
Question by:Troy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329315
can you post a sample excel so we can test the code...
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329317
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
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329323
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.

 
LVL 3

Author Comment

by:Troy
ID: 42329347
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
 
LVL 3

Author Comment

by:Troy
ID: 42329351
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
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329367
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
 
LVL 3

Author Comment

by:Troy
ID: 42329402
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
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329408
MS Office 2016 Plus :)
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329410
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
 
LVL 3

Author Comment

by:Troy
ID: 42329411
where am i testing this as it doesnt appear to be a valid link
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329412
in excel, or when creating your link
0
 
LVL 3

Author Comment

by:Troy
ID: 42329413
I have just edited the hyperlink in your sample and it still takes me to the www. page
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329416
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
 
LVL 3

Author Comment

by:Troy
ID: 42329417
here is the file.
29062281.xlsm
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329425
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Join & Write a Comment

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question