CODEWITHSUNDEEP
excelvbaexcel-formulahyperlinkvba7
Bert
Bert

Reputation: 51

Excel Hyperlink function to open a specific page

Could anyone from this group can offer some advice on the problem I'm trying to solve? I was attempting to open a PDF document to a specific page using the Excel Hyperlink function. I tried to solve this mystery but to no avail. Hope someone can provide me their thoughts and ideas.

Excel Table

Main Folder

PDF Reference Folder

I tried Google and Youtube but only few were related to my issue and the solution discussed was not working on my situation... or maybe i missed something. This will be a great help for me once the issue is solved. Thank you in advance.

Upvotes: 5

Views: 687

Answers (1)

Tragamor
Tragamor

Reputation: 3634

Using a macro enabled workbook you can hi-jack the hyperlink open event but it isn't as dynamic as we would hope it to be. Unfortunately, we cannot cancel any followed hyperlink using Worksheet_FollowHyperlink so we need to find a way around following a direct hyperlink.

We can create a hyperlink which goes nowhere by referencing itself then set the text to display as "Follow Link". Because the cell reference is then in the hyperlink, we can use an offset from that to get the desired address and sub address from the column before it; which then allows us to open the PDF at the desired place.

hyperlink settings

Unfortunately, the hyperlink if copied down will still reference the original cell, so each link would need to be edited separately. To date I haven't found a way to use the HYPERLINK function successfully.

The hyperlink format in the cell to the left would need to be the full path appended with "#page=" and the relevant page; e.g. C:\links\blah.pdf#page=6

In the worksheet module

Const AcrobatReader = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.TextToDisplay = "Follow Link" Then
        Dim Ref As String: Ref = Range(Target.SubAddress).Offset(0, -1).Text
        Dim URL() As String: URL = Split(Ref, "#page=")
        If UBound(URL) > 0 Then Call OpenPDFtoPage(URL(0), CLng(URL(1)))
    End If
End Sub

Function OpenPDFtoPage(FilepathPDF As String, page As Long)
    Dim Path As String: Path = AcrobatReader & " /A ""page=" & page & """ " & FilepathPDF
    Shell Path, vbNormalFocus
End Function

The AcroRd32.exe path may need updating for specific installations

Maybe having just one "Follow Link" hyperlink where the URL in the cell next to it is more dynamic may allow this to be a bit more useable.

Upvotes: 1

Related Questions