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