VBA – Open a PDF to a Specific Page

Once again, I was trying to help someone in a forum who was looking to open a PDF document and goto a specific page using Acrobat Reader.

A quick Google, and you can easily find out that Acrobat Reader accepts several command line switches/parameters. As such, I put together the following little procedure to do exactly that, open a PDF to a specific page.

'---------------------------------------------------------------------------------------
' Procedure : OpenPDF
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open a PDF on a specific page
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
' Reference : http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/pdf_open_parameters.pdf
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified pathe and filename, including extension, of the PDF to
'             open.
' page      : Page number to open the document at
' zoom      : Numerical value representing a zoom factor; 100=100%, 65=65%, ...
' pagemode  : Displays bookmarks or thumbnails; bookmarks, thumbs, none
' scrollbar : Turns scrollbars on or off; 1=Turn on, 0=Turn off
' toolbar   : Turns the toolbar on or off; 1=Turn on, 0=Turn off
' statusbar : Turns the status bar on or off; 1=Turn on, 0=Turn off
' messages  : Turns the document message bar on or off; 1=Turn on, 0=Turn off
' navpanes  : Turns the navigation panes and tabs on or off; 1=Turn on, 0=Turn off
'
' Usage:
' ~~~~~~
' OpenPDF "C:\Users\Daniel\Documents\Test\Test.pdf",3,,"none",1,0,0,0,0
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-Dec-04                 Initial Release
' 1         2013-Dec-04                 More advanced options added
'---------------------------------------------------------------------------------------
Function OpenPDF(sFile As String, _
                 Optional page, _
                 Optional zoom, _
                 Optional pagemode, _
                 Optional scrollbar, _
                 Optional toolbar, _
                 Optional statusbar, _
                 Optional messages, _
                 Optional navpanes)
    On Error GoTo Error_Handler
    Dim WSHShell        As Object
    Dim sAcrobatPath    As String
    Dim sParameters     As String
    Dim sCmd            As String

    'Determine the path to Acrobat Reader
    Set WSHShell = CreateObject("Wscript.Shell")
    sAcrobatPath = WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\AcroRd32.exe\")

    'Build our parameters
    If Not IsMissing(page) Then
        If Len(sParameters) = 0 Then
            sParameters = "page=" & page
        Else
            sParameters = sParameters & "&" & "page=" & page
        End If
    End If
    If Not IsMissing(zoom) Then
        If Len(sParameters) = 0 Then
            sParameters = "zoom=" & zoom
        Else
            sParameters = sParameters & "&" & "zoom=" & zoom
        End If
    End If
    If Not IsMissing(pagemode) Then
        If Len(sParameters) = 0 Then
            sParameters = "pagemode=" & pagemode
        Else
            sParameters = sParameters & "&" & "pagemode=" & pagemode
        End If
    End If
    If Not IsMissing(scrollbar) Then
        If Len(sParameters) = 0 Then
            sParameters = "scrollbar=" & scrollbar
        Else
            sParameters = sParameters & "&" & "scrollbar=" & scrollbar
        End If
    End If
    If Not IsMissing(toolbar) Then
        If Len(sParameters) = 0 Then
            sParameters = "toolbar=" & toolbar
        Else
            sParameters = sParameters & "&" & "toolbar=" & toolbar
        End If
    End If
    If Not IsMissing(statusbar) Then
        If Len(sParameters) = 0 Then
            sParameters = "statusbar=" & statusbar
        Else
            sParameters = sParameters & "&" & "statusbar=" & statusbar
        End If
    End If
    If Not IsMissing(messages) Then
        If Len(sParameters) = 0 Then
            sParameters = "messages=" & messages
        Else
            sParameters = sParameters & "&" & "messages=" & messages
        End If
    End If
    If Not IsMissing(navpanes) Then
        If Len(sParameters) = 0 Then
            sParameters = "navpanes=" & navpanes
        Else
            sParameters = sParameters & "&" & "navpanes=" & navpanes
        End If
    End If

    'Open our PDF
    If Len(sParameters) = 0 Then 'No parameters
        Shell sAcrobatPath & " " & Chr(34) & sFile & Chr(34), vbNormalFocus
    Else 'Parameters
        'Open the file using Shell (no prompt)
        sCmd = sAcrobatPath & " /A " & Chr(34) & sParameters & Chr(34) & " " & Chr(34) & sFile & Chr(34)
        Shell sCmd, vbNormalFocus
        '        'Open the file using FollowHyperlink (user will get prompts)
        '        sCmd = Replace(sFile, "\", "/") & "#" & sParameters
        '        Application.FollowHyperlink sCmd
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set WSHShell = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenPDF" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

23 responses on “VBA – Open a PDF to a Specific Page”

  1. Brian

    I don’t normally leave comments but I had to leave one because this code is elegant and works perfectly. I have been searching all over for something like this. Nice work!

  3. Chris Whisson

    Hi there, thanks very much for this piece of code it has been very useful and very well written! However on one of my computers it gives me an error when I try to use it! “Invalid root in registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\AcroRd32.exe\*”
    Are you able to tell me how to fix this?

    1. Daniel Pineault Post author

      I’m not sure.
      I’d start by manually reviewing the registry, check the executable being launched, perhaps you have a different version.

  4. Dave Fetters

    I had a VBA subroutine that opened one of my .pdf files to the correct page, but then I tried it on another .pdf file. For a reason I may never discover, that file would only open to the first page (same computer, same code, only difference was the .pdf itself). Your routine opened that second file – THANK YOU!

  6. Frank

    Hello.
    Thank you very much for this excellent code!
    I have one question though. I use this to open the same pdf at different pages by doubleclicking on the page number I have stored in a table. The first time I click, it opens the file at the page asked, but if I then click at a different number it doesn’t work! It just brings the open document to the front but still focused at the previous page!
    Do you have any idea what could be happening? I presume it is because the file is already open and so it doesn’t execute the piece of code that says about the page number.

    kind regards,
    Frank

    1. Daniel Pineault Post author

      Frank,

      Yes, I’m assuming it is because the file is already open. I don’t have a solution for you at them moment, but will see what I might be able to dig up.

      2. Daniel Pineault Post author

        Frank,

        I did some digging and sadly I haven’t found a real solution. It can easily be automated through VBA if you have the full version of Acrobat (but 99% do not) and sadly automating the Reader is not possible.

        My thought would be to check and see if the PDF in question is open, if it is close it, and then reopen using the code to simulate switching pages, but this is far from elegant.

        Another option would be to open the PDF through IE and you can include the page number as part of the URL, so by changing the URL string you can switch pages.

  7. Max

    Hi Daniel
    This is a fantastic code, but I am facing a problem that I don’t know it is only me or it is common!
    That is when I start the code it pulls up AcroRd32.exe but gives an error also that cannot find the file (my pdf file)!
    However, if I pull up the pdf file manually once and close then the code works without any problem!
    This causes that I in the morning when start my work to pull up some 20 pdf files manually first and then close them so that I can use the code to pull up the file during the day whenever needed.
    Is there a way around it?
    FYI I am using Windows 7 Prof and Excel version 10
    Thanks
    Max

    1. Daniel Pineault Post author

      I never seen such behavior, nor has anyone reported anything like it before.

      Just a stab in the dark, but is it possible , if this occurs when you first log on, that your mapped drives are not reconnected when you first run it. By manually opening them, you are in fact forcing the reconnection and thus everything works afterwards. My gut is telling me this is an IT issue, not an Access or function issue. But as I said, this is just a stab in the dark.

  8. Sam Shaheen

    Daniel
    How do I put this code behind a command in a form
    “Private Sub Command54_Click()

    “End Sub

    1. Daniel Pineault Post author

      Copy/paste the procedure into a Standard Module and follow the usage example.

      Below are a couple examples for you to inspire yourself from.

      Private Sub Command54_Click()
    OpenPDF "C:\Users\Daniel\Documents\Test\Test.pdf", 3, , "none", 1, 0, 0, 0, 0
End Sub

Private Sub Command54_Click()
    Call OpenPDF("C:\Users\Daniel\Documents\Test\Test.pdf", 3, , "none", 1, 0, 0, 0, 0)
End Sub

Private Sub Command54_Click()
    Call OpenPDF("C:\Users\Daniel\Documents\Test\Test.pdf")
End Sub
  9. Robert

    Works perfectly, is it possible to have it work when the pdf is already open?
    Because if the user got to the right page, and then want to go to another page in the pdf from the excel file (page number is linked to active cell) it stays on the page it first opened to.

    Perhaps have it automatically close the pdf first?

    1. Daniel Pineault Post author

      If you have Adode installed (not just the reader) it may be possible through automation.

      It may also be possible through advanced APIs and calls to switch the page in the reader, but IMHO the easiest solution is to close the Adobe reader instance and then run the procedure again for the desired page.

      That said, you’ve got me curious, so I may try and find time this week to look into the API approach, no promises as I am swapped right now.

  10. Pierre-Olivier Lapointe

    Hello Daniel,
    Thank you for this work, it is really useful. Can you please help me with a single specification. I want to specify a “page Label” instead of the actual page number. What would be the function to call?

    Thanks,

    Pierre-Olivier

  11. Filipe Oliveira

    Hi Daniel,
    Trust you’re doing well.
    Thanks a million for the code, really neat and extremely useful.
    The only problem I have is to set the zoom. It always opens the file on 166%.

    On the code, I have set twice for 100 but does not obey it:
        If Len(sParameters) = 0 Then
            sParameters = "zoom=50" & zoom
        Else
            sParameters = sParameters & "&" & "zoom=50" & zoom
        End If

    Not sure what I am doing wrong here.
    Thanks a million and best regards,
    Filipe Oliveira

    1. Daniel Pineault Post author

      But you have set 50 in the code and then specify another value when you call the function? If you want to hard code it, then no need for the ‘& zoom’, otherwise, leave the original code as is and simply pass 100 as the zoom input variable when calling the function. Perhaps if you post your function call I could help you more.