VBA Macro to open URLs in Chrome

[Kris84]

Dear Forum,

I was wondering if anyone could help me get to a solution to this issue.

Basically on my excel sheet I have a list of URLs (not hyperlinked ie. clickable) all listed under Column G. Column G can only be populated with URLs from cell G4 to cell G104; however it might also contain empty cells - which in this case I'd like the code to skip / ignore and move to the next URL under it.

Getting to here did not seem to be as hard as I thought as I found plenty of solutions online however I specifically need these URLs to be opened in Chrome and in seperate tabs (one tab per URL) and not windows. Access certificates for these URLs are only enabled on the Chrome browser and I have no control on this; hence it's imperative that the code uses Chrome.

Is there any way I could use VBA to get this done? Unfortunately my coding proficiency is not of the highest level.

I appreciate any help and thank you in advance for any tips you might provide.

Chris

 

[Kris84]

Hi again!

Upon some further research I managed to get to this:

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

It 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) 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?

Thank You once more for your time!
Chris