Introduction.
Opening an external file (such as Word, Excel, or Adobe PDF) from Microsoft Access is straightforward. You can use the Hyperlink tool (Ctrl+K) to browse for a file on disk and assign it as a hyperlink on a form. Another option is to open the Hyperlink tool from the Hyperlink Address property of a label, then browse for and select the desired file.
Once the hyperlink is set, clicking on it will open the file (Word, Excel, or PDF) starting with the first page, according to the document’s default view settings.
The Shell Command.
Another method used to open an external file is a DOS Command (Microsoft Disk Operating System) Shell in VBA. The Shell() command needs mainly two parameters (actually three values), as the syntax is shown below:
Call Shell(“<Parent Application> <file pathname>”, <window mode>)
The first parameter of the Shell() command has two segments, separated by a space.
A. First Parameter
The parent Application Path Name(C:\Program Files (x86)\adobe\Reader 9.0\Reader\AcroRd32.exe).
The pathname of the PDF file to open (
C:\aprpillai\Documents\dosa.pdf).
B. Second Parameter
- open window mode
The Sample Trial Run.
Let us try an example to open a PDF file: C:\aprpillai\Documents\dosa.pdf using the Shell() Command:
Open any one of your databases or create a new one.
Create a new Form with a Command Button on it.
Select the Command Button and open its Property Sheet (F4).
Change the Name property value to cmdRun and change the Caption Property value to Open PDF File.
Select the Event Tab of the Property Sheet and click on the On Click Property.
Click on the build (. . .) button at the right end of the property to open the VBA Module.
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
Change the pathname of the PDF file to select a file from your disk with a few pages.
Save the Form with the name PDF_Open_Example.
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 in the navigation control at the bottom of the document to jump to that page.
However, if we already know the page number we want to open, we can pass it as a parameter to the AcroRd32.exe program. For example:
..\AcroRd32.exe /A page=25 ..\dosa.pdf
This command will open the PDF file directly to page 25 instead of starting from the first page.
To demonstrate, let’s modify our earlier program by adding the page parameter (/A page=25) so that the PDF opens at the 5th page. The updated version of the program is shown 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
Note: Do not include spaces on either side of the equal sign in the parameter page=25. The /A switch must immediately follow the program name (AcroRd32.exe), with a space before specifying page=25.
In our example, the file dosa.pdf contains several recipes. To make navigation easier, we should be able to jump directly to a specific recipe with a single click. For this purpose, we can create a Combo Box on the form that lists all recipes, along with their corresponding page numbers and descriptions. By selecting a recipe from the list, the program can pass the correct page number to Acrobat Reader, allowing us to quickly display the chosen recipe.
A Sample Form.
An 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) a little later.
Open the Form in Design View.
Select the Control Wizard tool to launch when you select the Combobox Tool.
Select the Combobox Tool and draw a Combobox on the Form.
Select the Radio Button on the Control Wizard, with the caption ‘I will type the values that I want and click Next.
Type 2 in the ‘Number of Columns’ control and press the Tab Key.
Type a similar list of topics, shown in the image above, from your PDF file with Page Number in the first column and Description in the second column. When finished, click Next.
Select the first column and click Next.
Type a suitable caption for the child label and click Finish.
Select the Combo Box; if it is deselected, then display the Property Sheet (F4).
Change the Name Property value to cboPage.
Display the VBA Module of the Form (Design -> Tools -> View Code or press ALT+F11).
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
Close the VBA Module, save the Form, and open it in normal view.
Select one of the items from the Combobox with a larger page number.
Click on the Command Button to open the PDF file displaying the selected page. Check the following image for a sample view of the dosa.pdf file:
The Zoom Parameter.
From the header toolbar, you can see that the current view shows page 7 of 51, with the document opened at about 60% zoom of its actual size. This zoom level can also be controlled programmatically. By specifying the Zoom parameter immediately after the Page parameter, we can open the PDF document to both the desired page and zoom percentage.
To demonstrate this, I created a second Combo Box control on the form, named cboZoom, which contains a list of zoom percentage values: 50, 60, 70, 80, 90, 100, and 120. By selecting one of these values along with the page number, the PDF document can be opened not only at the correct page but also at the preferred zoom level for easier viewing.
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 combine the parameter key names (page, zoom) with the control values (page number and zoom percentage), the result should be the sample value shown earlier.
To test this, you can place a Text Box on the form with the name cboZoom. Enter a zoom percentage value in this control (note: do not include the % symbol), then run the code to confirm that it works.
Important: If both controls—cboPage and cboZoom—are left empty, the PDF file will open by default with the first page on top and at 100% zoom.
No comments:
Post a Comment
Comments subject to moderation before publishing.