Linking to a Specific Page in a PDF File

Responding to Derek regarding the 'Dim objIE As New Internet Explorer' returning 'User defined type not defined.' error.
From a Chip Pearson comment from 2006 at www.excelforum.com,

You need to add a reference to the "Microsoft Internet Controls"
library. In VBA, go to the Tools menu, and choose References. In
that dialog, scroll down to "Microsoft Internet Controls" and put
a check next to it. This is the DLL that contains the definitions
of the objects you are trying to use.

That worked for me...error went away.

Additional Web searches suggested using the Excel "Worksheet_FollowHyperlink" Event. The following code uses that process. The trick is to set up the link(hyperlink) cell with a hyperlink pointing to "itself" (I.E. the actual address of the cell with which you want the hyperlink to work).

Please note that the Excel =HYPERLINK function is NOT the same as the Hyperlink object within VBA. The HYPERLINK function will not work as everyone has found out. the HYPERLINK function does not fire off the FollowHyperlink event.

You can set a Hyperlink via the standard Excel dialog box or set it programmatically (via VBA) which I do in a separate process.

Part of my code used RegKeyRead(s) to determine the default application for a PDF file. It may or may not work for you.
OR you could use
strProgram = Range("AcrobatApplication")
where the "AcrobatApplication" named range contains the full path/file for Acrobat.
I don't like setting a fixed program path/name in VBA. Setting named range in the workbook is more appropriate, or even determining the default application "on-the-fly" if it works.

The "Worksheet_FollowHyperlink(ByVal Target As Hyperlink)" is a WORKSHEET Event, and as such is located on each Excel sheet (macro area). It does not work in the workbook MODULES code area.

Finally, there could probably be more error checking, but I use this for myself and it is sufficient (for now) until things go haywire in some unexpected way.....

CODE:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim strLink As String
Dim strPage As String
Dim strParam1 As String
Dim strParam2 As String
Dim strProgram As String
Dim strSheetName As String
Dim strPDFAssoc As String
Dim strPDFOpen As String
Dim strTemp As String

' Define the Registry Keys as Constants. All are included for completeness.
Const HKEY_CLASSES_ROOT As Long = &H80000000
Const HKCR As Long = HKEY_CLASSES_ROOT
Const HKEY_CURRENT_USER As Long = &H80000001
Const HKCU As Long = HKEY_CURRENT_USER
Const HKEY_LOCAL_MACHINE As Long = &H80000002
Const HKLM As Long = HKEY_LOCAL_MACHINE
Const HKEY_USERS As Long = &H80000003
Const HKU As Long = HKEY_USERS
Const HKEY_PERFORMANCE_DATA As Long = &H80000004
Const HKPD As Long = HKEY_PERFORMANCE_DATA
Const HKEY_CURRENT_CONFIG As Long = &H80000005
Const HKCC As Long = HKEY_CURRENT_CONFIG
Const HKEY_DYN_DATA As Long = &H80000006
Const HKDD As Long = HKEY_DYN_DATA

strHyperLinkAddress = ActiveCell.Hyperlinks(1).Address
strHyperLinkSubAddress = ActiveCell.Hyperlinks(1).SubAddress
strSheetName = ActiveSheet.Name
strCellAddress = Replace(ActiveCell.Address, "$", "")

' Exit if hyperlink is not pointed to 'itself' because this routine is based on the self link.
' The HyperlinkSubAddress should contain the cell self-address, indicating the self link.
' The HyperLinkAddress is not used in this routine.
If strHyperLinkSubAddress <> "'" & strSheetName & "'!" & strCellAddress Then
Exit Sub
End If

' The file and page number are stored in different columns of the same row.
strLink = ActiveCell.Offset(0, 2).Value 'retrieve the file to link
strPage = ActiveCell.Offset(0, 1).Value 'retrieve page #
If strPage = "" Then
strPage = 1
End If

strParam1 = " /A page="
strParam2 = "=OpenActions "

'Determine the default application for the file extension, PDF.
strPDFAssoc = RegKeyRead("HKCR\.pdf\")
strPDFOpen = RegKeyRead("HKCR\" & strPDFAssoc & "\shell\open\command\")
strTemp = Replace(strPDFOpen, """", "") 'remove double quotes
strProgram = Replace(strTemp, " %1", "") 'remove the %1

'Calls a Command Prompt to open Adobe Acrobat and then open the file at the specified page number.
retVal = Shell(strProgram & strParam1 & strPage & strParam2 & Chr(34) & strLink & Chr(34), 1)

End Sub

Private Sub CommandButton1_Click()
Sub OpenPDFpage()
Dim myLink As String
Dim TargetPage As Double
Dim objIE As New InternetExplorer

myLink = "F:\Standards\Codes and Standards\AWS\AWS D1.1/AWS D1.1-D1.1M-2015.pdf"
TargetPage = 179 'Page number to be shown

With objIE
.Navigate myLink & "#page=" & TargetPage
.Visible = True
End With
End Sub

Please suggest whether my macro is correct or not

I'm desperately trying to get this to work using Excel 2016 with no luck. With the code as is (substituting my local file path and name) I get a compile error at 'Dim objIE As New InternetExplorer' saying 'User defined type not defined'. I would dearly love this to work as a function so I could pass the page parameter. Any thoughts on getting it working. Many thanks.

Hi Allen
No success for Excel 2010. PDF-file is ever open on the page 1. Also another parameters as zoom etc. are ignored.
A relatively positive message is, that in Excel 2013 it works completely OK.
Is there something wrong with my Excel 2010?
Petr

How can I do this is Excel

1. How to create a hyperlink from a scanned pdf file saved in my local folder

Ex.
Invoice: Amount: Scanned Invoice Pdf:
123 $2,000 SIPDF12345
124 $2,000 SIPDF12346

Will there be a macro wherein it will automatically open the pdf file from scanned invoice pdf folder saved in local folder(e.g.,SIPDF12345 & SIPDF12346).

How do I convert the code in the original posting to a function?

I need to open multiple PDF files to different page numbers in the same workbook and I would like to create a function. I need them to open from my local drive and would like them to open using a function so that other users of the function do not need to alter the source code in excel.

I have it working right now where it opens in Windows Explorer, but that does not allow the users to edit the PDF document once open.

Here is my function that works for Windows Explorer:

=HYPERLINK(GoToPDFpage("C:UsersDesktopBinder1.pdf",3),"LINK")

Here is the source code behind it: (How do I change it so that it opens the Adobe Reader or Acrobat instead of IE?)

Function GoToPDFpage(Fname As String, pg As Integer)

Set IE = CreateObject("InternetExplorer.Application")

With app
.Navigate Fname & "#page=" & pg
.Visible = True
End With
End Function


Please help! Thanks,

Hi there, thank you all for valuable tips. In fact, the hyperlink function works well for me and I can open the pdf file at the desired page using the =hyperlink("E:testgary.pdf#page=5") line. However, is there a way to take a number, say ‘5’, automatically from another column and implement it in the formula? This would let me to avoid editing the hyperlinks once in a cell.
Thanks in advance!

How can I hyperlink to a certain page of a PDF document if I have embedded that PDF within my Excel spreadsheet?

Attempted to replicate Richard Chapman experiment, but could not get the link to go to a specific page. It always opens on page 1 using

=hyperlink("E:testgary.pdf#page=5")

Using Excel 2010.

Hey guys,
One of the ways I did this as I was hyperlinking an excel doc to a specific page in a PDF. I save the PDF on the page that I wanted. Then I hyperlinked it. As long as the PDF doesn't get saved again on a different page or you don't need multiple specific pages then this may be good for you. Good luck

There is a way i figured out where you can use the #page=[page#]. without vb! the catch is that you have to have the excel file on the same drive as the file you are linking it to.

so.

in the command for the cell =(hyperlink"","")

use
\[drive][file path]
insetead of
drive:\[filepath]

this is the only things that has worked for me

let excel write and run a batch file to open the pdf. Edit the file paths and if you are bothered about leaving the bat file move the kill command to a workbook close event.

Sub WriteAndRunBatFile()
Dim retVal
strFilePath = "c:folderpdf.bat"
pg = 2
Open strFilePath For Output As #1
Print #1, "Start /Max /w " & Chr(34) & "Current E-book" & Chr(34) & " " & Chr(34) & "C:Program Files (x86)AdobeReader 11.0ReaderAcroRd32.exe" & Chr(34) & " /a " & Chr(34) & "page=" & pg & Chr(34) & " " & Chr(34) & "H:DocumentsRPGDragonlanceNew folderSample File.pdf" & Chr(34) & ""
Close #1
retVal = Shell(strFilePath)
'Kill strFilePath
End Sub

SORRY, but Excel 2010 using the HYPERLINK() and specifying the page number using the #page=5 DOES NOT WORK. I have tried this numerous different ways with different files and it just does NOT work. DOES anyone have a way to Hyperlink() to a PDF file and go to a SPECIFIC PAGE?

I have a Excel that I am trying to add hyperlinks to go to a specific page on a pdf file. How do I do that with vba macro?

To my opinion, the shorest and simplest macro, to open a saved PDF file in page 4, will be:
------------------
Sub Micky()
App_Path = "C:Program FilesAdobeReader 10.0ReaderAcroRd32.exe"
File_Path = "D:TEST444.pdf"
Page_Num = 4
Shell App_Path & " /A Page=" & Page_Num & " " & File_Path, vbMaximizedFocus
End Sub
---------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

To echo AW's comment, the HYPERLINK("file.pdf#page=3","clicktoseePg3") does not open to page 3 of the named PDF document, it brings up only page 1 of the same document.

The above method does not work if you print out as PDF using the Adobe Acrobat driver. Nothing happens if you click on the hyperlink in the PDF, because Adobe can not interpret weblinks made using the "=hyperlink()" function.

A simple work-around is to mark the whole spread sheet in MS Excel, copy, and in Adobe select "Create PDF from clipbook". Then the hyperlink function works.

I have a word doc that I am trying to add hyperlinks to go to a specific page on a pdf file. How do I do that with vba macro?

Excel Office 2010 on Win 7 64 bit
Hyperlink to a specific pdf page:
http://Area%20Riservata1/J.ch%20-%20Cent.pdf?#page=851

The above Hyperlink work on office 2003 WinXP with office 2010 it don't work because of: in browser IE 10-11 the link is .......Cent.pdf%20-%20page=851.
20%-20% instead of #
Please could you help me? I don't want to use a macro but a HL in a cell.

thanks

Thanks again Richard. However, I tried it and it changes the # sign to a %20-%20 in the web browser (Google Chrome) and then says file is not found. If I change the url manually in my browser back to the # sign it works. I also tried this for a pdf residing on my dropbox account, and it pulls up the file, but then does not search it. Does this parameter not work for a https url?

Grant, the HYPERLINK function's open parameters don't work on PDF files stored on your computer but it can work for PDF files that can be found using a URL (http://) file location.

This works for me (using Excel 2010):

In cell A1, enter the URL + the first part of search open parameter, for example: http://partners.adobe.com/public/developer/en/acrobat/PDFOpenParameters.pdf#search=

In cell A2, enter the desired search term(s), for example:
wordList

In cell A3, enter the hyperlink function pointing to cells A1 & A2, for example:
=HYPERLINK(A1&A2,"link to PDF with a search open parameter")

Change the search term in cell A2 to open the PDF file highlighting different search term(s).

Thanks Richard. Unfortunately, it appears the pdf search does not work, as it seems to ignore my search parameters and continue opening the file to the first page. I'm linking to a local pdf if that makes a difference. Also, is is possible to use a cell reference to search for the value in an adjacent cell instead of having to type the search item in manually. Any ideas would be appreciated. Thank you.

You can put the URL directly in a cell in Excel (no macro required). Click Insert>Hyperlink and put the URL in the Address field: [ http://partners.adobe.com/public/developer/en/acrobat/PDFOpenParameters.pdf#search="PersonsLastName" ]. In the Text to display field, enter whatever text is desired. For example: Click HERE to open the PDF document to see more information about this Person's Name.

NOTE: You will need to find a search term that appears only once in the PDF document for it to open on the specific page. If the "PresonsLastName" appears more than once in the PDF document, it will open on the first page with the search navigation window pre-populated with the various locations the PersonsLastName appears in the document. Spaces in the #search parameter setting means OR for the search conditions.

Adobe defines parameters that allow you to open a PDF document with a command or URL that specifies exactly what to display (a named destination or specific page), and how to display it (using such characteristics as a specific view, scrollbars, bookmarks, annotations, or highlighting). See details at http://partners.adobe.com/public/developer/en/acrobat/PDFOpenParameters.pdf#search="page=pagenum"

Any ideas on how this could be modified to search for the value of the cell in the pdf and go to that page. I.E. the cell has a person's name in it, and by clicking on the cell, it will hyperlink to the pdf and search for the name in the pdf and go to that page?

Thanks,

If you:
(1) need a macro (e.g. to add button to QAT or Ribbon)
AND
(2) want to open only one fixed file
AND
(3) do not need to open a specific page

the following very simple VBA macro works perfectly:

Sub Array_Formula_Help()
ThisWorkbook.FollowHyperlink _
"C:UsersDELLDocumentsExcelAmazing Array Formulas tipsETAF3E.pdf"
End Sub

Thanks PL.ER.RAJAN, It works, perfect for me :D

Any ideas on how this could be modified to search for the value of the cell in the pdf and go to that page. I.E. the cell has a person's name in it, and by clicking on the cell, it will hyperlink to the pdf and search for the name in the pdf and go to that page?

Thanks,

With some modification it worked as below:

create a function as below:

Function OpenPDFpage(myLink As String, TargetPage As Double)
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate myLink & "#page=" & TargetPage
.Visible = True
End With
End Function

In excel enter a formula
in column C
=IF(D9="Y",OpenPDFpage(E9,F9),"-")

D, Y or N
E, Full path and file name
F, Page no


thanks,

I too was trying to link to an internal PDF page and tried the HYPERLINK function. It does not link into an internal page. And I am using v2010.

However, if the Hyperlink dialog box [not the =HYPERLINK() function] is used to create the link, it DOES work.

Using #page= does not work if the file is stored locally, at least it's not working for me. From what I've read online using #page= only works if you're hyperlinking to a URL

In v2010, using #page=5 as the target in the hyperlink formula works without a macro.

=HYPERLINK("E:testgary.pdf#page=5")