Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Opening Specific Page of Pdf File

Opening an external file (Word, Excel or Adobe .pdf file) from Microsoft Access is not a big issue.  We can use the HyperLink tool (Ctrl+K) to browse and find a file on disk and setup a Hyperlink on a form.  We can launch the Hyperlink tool from the Hyperlink Address Property of a Label, browse and find the external file on disk and set up a hyperlink on a label.

When we click on the Hyperlink the file (Ms-Word, Excel or Adobe Acrobat pdf file) will open with the first page on top and whatever default view configuration of the document is set.

Another method used to open an external file is DOS (Microsoft Disk Operating System) Shell() Command in VBA.  The Shell() command needs mainly two parameters (actually three values), as the syntax shown below:

Call Shell(“<Parent Application> <file pathname>”, <window mode>)

The first parameter of the Shell() command have two segments, separated with a space.

A.  First Parameter

  1. The parent Application Path Name(C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe).
  2. The path name of the pdf file to open (C:\aprpillai\Documents\dosa.pdf).

B.  Second Parameter

  1. open window mode

Let us try an example to open a PDF file: C:\aprpillai\Documents\dosa.pdf using the Shell() Command:

  1. Open any one your databases or create a new one.
  2. Create a new Form with a Command Button on it.
  3. Select the Command Button and open it's Property Sheet (F4).
  4. Change the Name property value to cmdRun and change the Caption Property value to Open PDF File.
  5. Select the Event Tab of the Property Sheet and click on the On Click Property.
  6. Click on the build (. . .) button at the right end of the property to open the VBA Module.
  7. Copy and paste the following VBA Code overwriting the existing lines:
    Private Sub cmdRun_Click() 
    Dim strApplication As String 
    Dim strFilePath As String 
    strApplication = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe" 
    strFilePath = "C:\aprpillai\Documents\dosa.pdf" 
    Call Shell(strApplication & " " & strFilePath, vbNormalFocus) 
    End Sub
  8. Change the pathname of the pdf file to select a file from your disk with few pages.
  9. Save the Form with the name PDF_Open_Example.
  10. Open the form in normal view and click on the Command Button to open the pdf file.

The above Sub-Routine opens the file selected with Normal Focus.

After opening the PDF file with page 1 on the top, type a different page number on the navigation control, at the bottom of the document, to jump to that page.

But, if we know this information in advance then we can use the page number value as open parameter to AcroRd32.exe program (..\AcroRd32.exe /A page=25 ..\dosa.pdf) to open the pdf file with that page in view.

We will modify the above program with the addition of Page parameter (..\AcroRd32.exe /A page=25) to jump to the 5th page of the pdf document. Modified program is given below:

Private Sub cmdRun_Click()
Dim strApplication As String
Dim strFilePath As String

strApplication = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A page=25"
strFilePath = "C:\aprpillai\Documents\dosa.pdf"

Call Shell(strApplication & " " & strFilePath, vbNormalFocus)

End Sub

NB: Don't leave any space on either side of the equal sign in ‘page=25’ parameter.  The /A switch must be given after the program name AcroRd32.exe, followed by a space and then page=25.

We have several recipes on the dosa.pdf file and we must be able to go to a particular one quickly with a single click.  To make the task easy we must create a Combo box on the form with a list of all recipes (with page numbers and description) so that we can select a particular recipe and jump to that quickly.

Image of a sample form with the list of Dosa Recipes in a Combo Box is given below:

I will explain the second Combo box (Zoom Percentage) little later. 

  1. Open the Form in Design View.
  2. Select the Control Wizard tool to launch it when you select the Combobox Tool.
  3. Select the Combobox Tool and draw a Combobox on the Form.
  4. Select the Radio Button, on the Control Wizard, with the caption ‘I will type the values that I want’ and click Next.
  5. Type 2 in the ‘Number of Columns’ control and press Tab Key.
  6. Type a similar list of topics, shown in the image above, from your pdf file with Page Number in first column and Description in the second column, when finished click Next.
  7. Select the first column and click Next.
  8. Type a suitable caption for the child label and click Finish.
  9. Select the Combo Box, if it is de-selected, then display the Property Sheet (F4).
  10. Change the Name Property value to cboPage.
  11. Display the VBA Module of the Form (Design - - > Tools - - > View Code or press ALT+F11).
  12. Copy and paste the following VBA Code into the Module overwriting the existing code:
    Private Sub cmdRun_Click()
    Dim ReaderPath As String
    Dim pdfFilePath As String
    Dim PageNumber As Integer
    Dim strOpenPDF As String
    PageNumber = Nz(Me![cboPage], 1)' Get user selected page number, if empty then take 1 as default
    ReaderPath = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A " & "page=" & PageNumber 
    pdfFilePath = "C:\aprpillai\Documents\dosa.pdf" 'change the path to match your file location
    strOpenPDF = ReaderPath & " " & pdfFilePath
    Call Shell(strOpenPDF, vbNormalFocus)
    End Sub
  13. Close the VBA Module, Save the Form and open it in normal view.
  14. Select one of the item from the Combobox with a larger page number.
  15. Click on the Command Button to open the pdf file displaying the selected page. Check the following imge for a sample view of the dosa.pdf file:

    As you can see from the header toolbar that the page in view is 7/51 and the document view is only about 60% (zoom=60) of it's actual size of 100%. We can control the view size of a pdf document by specifying the Zoom parameter immediately after the Page parameter.

    I have created a second Combo Box Control with the name cboZoom on the form, image shown above, and Zoom Percentage values 50,60,70,80,90,100 & 120 so that one of these values can be selected along with the Page number to view the document in the desired size.

The modified Code with the addition of Zoom Parameter is given below:

Private Sub cmdRun_Click()
Dim ReaderPath As String
Dim pdfFilePath As String
Dim PageNumber As Integer
Dim intZoom As Integer
Dim strOpenPDF As String

PageNumber = Nz(Me![cboPage], 1)
intZoom = Nz(Me![cboZoom], 100)

ReaderPath = "C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe /A " & quot;page=" & PageNumber & "&zoom=" & intZoom
pdfFilePath = "C:\aprpillai\hostgator\dosa.pdf"

strOpenPDF = ReaderPath & " " & pdfFilePath
Call Shell(strOpenPDF, vbNormalFocus)
End Sub

The Page parameter and Zoom parameter values must be joined with an & symbol and there should not be any spaces on either side of the equal (=) sign:

..\AcroRd32.exe /A page=7&zoom=60 C:\aprpillai\Documents\dosa.pdf

When you join parameter key names (page, zoom) and control values (page number and zoom percentage) together the result of it should form like the sample value shown above.

You may create a text box with the name cboZoom on the form and type the zoom percentage value (not to add the % symbol along with the value) and run to test the above code.

NB: If you leave both controls (cboPage and cboZoom) empty the pdf file will open with the first page of the file on top and with 100% zoom value.

Technorati Tags:

No comments:

Post a Comment


Your email address:

Delivered by FeedBurner


Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos Functions MS-Access Security Reports Animations msaccess animation msaccess forms Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards ms-access functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security DOS Commands Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Custom Functions Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess functions msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Copy Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Time Difference Union Query User Users Variables Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Form Recordset and Bookmarks

Bookmarks are stored on individual records of a Recordset, when loaded into memory on a Form.  When a Table or Query linked to a Form is ope...


Blog Archive

Recent Posts