VBA to open multiple hyperlinks

Hello,

I am using the following VBA code to open multiple URLs (hyperlinked) on Chrome in seperate tabs:

Sub Open_HyperLinks()
Dim chromePath As String, hl As Hyperlink


chromePath = Environ("PROGRAMFILES(X86)") & "\Google\Chrome\Application\chrome.exe"
If Selection.Count > 1 Then
Selection.SpecialCells(xlCellTypeVisible).Select
End If
'On Error Resume Next
For Each hl In Selection.Hyperlinks
Shell chromePath & " -url " & hl.Address
Next hl
End Sub

The code works almost perfectly and I can see the code attempting to open the links on chrome on seperate tabs. However I ran into another issue which I did not forsee in the beginning when attempting to tackle this project. All my hyperlinks (under one column (G)) are being generated by a formula in each cell: =HYPERLINK(G4) / =HYPERLINK(G5) and so on.

In theory, the macro does not seem to be opening the URL in hyperlink but the actual formula itself from which the hyperlink has been generated. Is there any way the above code can be tweaked for the macro to actually open the hyperlink I need to access?

If yes, could you please let me know what I am doing wrong? I have exhausted all possibilities I could think of even tried to look for answers on forums.

Thank You
Chris
Chris FenechRisk and Fraud ExecutiveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rgonzo1971Commented:
Hi,

Could you send a dummy?


EDIT You have probably to select Column G
Regards
0
Chris FenechRisk and Fraud ExecutiveAuthor Commented:
Hi!

Please find attached.

I will give you a quick rundown of what I accomplished so far - so perhaps it will be easier for you to comprehend the sheet's functionality and the goal behind it.

This is an excel sheet I am creating in order to open multiple URLs / hyperlinks at once on Chrome browser. The user will extract the account identifier from another report (which can be either a GUID or an email address) and then copy/paste under the respective column. A few rules to keep in mind perhaps:

> The first part of the URL (column B - hidden) will always be the same for every URL / account in question
> A GUID (Column C) will always comprise of a combination of numbers and letters. It will always have both.
> An email (Column E) will always contain the '@' symbol
> Each row on the sheet denotes a seperate account / customer. The account identifier (which will make the last part of the URL) will always either be a GUID or an email address. The user can never have both cells populated on the same row but the report can contain a mix of GUIDs or email addresses listed under different rows (as per example I have provided on the spreadsheet I was testing it on)

Formula in Column F (hidden) concatenates the full URL from Column B plus either Column C or E (depending which cell the user has populated)
Formula in Column G (hidden) differentiates between whether the information is originating from a GUID or from an email address as per criteria used. The tricky part here was to omit the integer '2' from the formula because as you can see every URL will be commencing with the following: https://controlpanel2 - hence the integer '2' will be present in every URL. I have replaced this with the remaining digits from 0 to 9 (excluding 2) which a GUID will incorporate.
Formula in Column H (hidden) turns the URL into a hyperlink

The buttons on the right will basically map the macro into opening the accounts on Chrome or refreshing the excel sheet to start over again. These are currently not working as I have not mapped them yet nor added the code for them - but that will not be an issue for me to do.

The excel has two macros - one to open the hyperlinks on Chrome (Open_Hyperlinks) and another code which I was testing which turns selected URLs into hyperlinks (HyperAdd)

The problem as I detailed below looks to be coming from the formula-generated hyperlinks which do not seem to open. When I try inputting a normal hyperlink myself manually (example: www.google.com) the code opens this hyperlink fine.

Please let me know if you require anything else from my end.

Thanks and Regards,
Chris
0
Rgonzo1971Commented:
You use selection

which range do you select?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Chris FenechRisk and Fraud ExecutiveAuthor Commented:
Hi Rgonzo1971,

I always select the final URL (cells in CoLumn H)

Thanks
Chris
0
Rgonzo1971Commented:
then try
Sub Open_HyperLinks()
Dim chromePath As String, strHl As String, strAddress As String


chromePath = Environ("PROGRAMFILES(X86)") & "\Google\Chrome\Application\chrome.exe"
If Selection.Count > 1 Then
Selection.SpecialCells(xlCellTypeVisible).Select
End If
'On Error Resume Next
For Each c In Selection
    strAddress = Split(Replace(Mid(c.Formula, InStr(1, c.Formula, "HYPERLINK(") + 10), ")", ""), ",")(0)
    strHl = ActiveSheet.Range(strAddress).Value
    Shell chromePath & " -url " & strHl
Next c
End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris FenechRisk and Fraud ExecutiveAuthor Commented:
I cannot thank you enough! Works like a charm!!

Thank you so very much for this; amazing work!
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
Chrome

From novice to tech pro — start learning today.