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. If you don’t have one then you may download the dosa.pdf file from this Link.
  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 pdf file: C:\aprpillai\Documents\dosa.pdf with Normal Focus in the Acrobat Reader window.

The dosa.pdf (Dosa is Indian Pancake) file have about 51 pages, with variety of recipes. Each recipe with different ingredients like Rava Dosa (on page 7), Sweet Dosa (on page 8), Set Dosa (page 5), Quick Dosa (page 6), Vella Dosai (Page 25), Moong Dosa (page 35) and so on. After opening the Dosa.pdf file with page 1 on the top if we know a particular recipe’s exact page location then we can type the 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 25th 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:

PDF File Launching Pad Image

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:

    PDF File View Image

    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: