[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Open
  • Priority: Medium
  • Security: Public
  • Views: 55
  • Last Modified:

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 ?
0
Troy
Asked:
Troy
  • 9
  • 7
16 Comments
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now