Introduction.
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 set up 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.
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 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 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 an open parameter to the 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 the Page parameter (..\AcroRd32.exe /A page=25) to jump to the 5th page of the pdf document. The 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 the ‘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 descriptions) so that we can select a particular recipe and jump to that quickly.
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 it 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 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 de-selected, 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.
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 its 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, the 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 it 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 a 100% zoom value.
No comments:
Post a Comment
Comments subject to moderation before publishing.