Open hyperlink within the VBA

Question

I have an excel file. In the first sheet there are rows of hyperlinks (Relative files paths = files are located next to the excel file).

In the VBA, I will obtain the cell that contain hyperlinks. How can I open the file which is followed by hyperlink in the VBA? Actually I want to open the hyperlink (open the file actually)

Answer 1

If the hyperlink in the worksheet is in a =hyperlink() formula we:

• grab the formula
• parse it to get the url
• follow the hyperlink:

Here is the code:

Sub ClickByVba()
    ary = Split(ActiveCell.Formula, Chr(34))
    ActiveWorkbook.FollowHyperlink Address:=ary(1)
End Sub

This will work for both links to the web and links to files.
If the link is to a file, the file will be opened (if not already open) and the jump will be made.

Answer 2

As I commented on Gary´s answer, the ActiveWorkbook.FollowHyperlink method works, but it will ask for your credentials every time you open the link, if your website requires a login.

If you wish to open a website and have your credentials saved, you can use this other function:

Sub OpenHyperlink(ByVal link As String)
   'Escape chars that cmd.exe uses
    link = Replace(link, "^", "^^")
    link = Replace(link, "|", "^|")
    link = Replace(link, "&", "^&")
    'Open default web browser
    Shell "CMD.EXE /C START " & link, vbHide
End Sub

Answer 3

Have a look at the Hyperlink object reference:

https://msdn.microsoft.com/en-us/library/office/ff835563.aspx

You should be able to open the file with the .Follow method, or if that doesn't work you can always grab the .Address property and pass that to the Workbooks.Open method.

Also bookmark the Excel Object Model Reference for future use :)

I don't think this works on formula hyperlinks, though, so if that's your situation then instead, do:

Sub h()
    Dim cl As Range
    Dim h As String
    Set cl = Range("E13") 'Modify to the cell containing your =HYPERLINK formula
    h = Replace(cl.Formula, "=HYPERLINK(", "")
    h = Replace(h, ")", "")
    h = Replace(h, Chr(34), "")
    h = Split(h, ",")(0)

    Dim wb As Workbook
    Set wb = Workbooks.Open(h)
End Sub