VBA Express : Excel - Manipulate PDF File with DDE Connection in VBA

Excel

Manipulate PDF File with DDE Connection in VBA

Ease of Use

Easy

Version tested with

2010

Submitted by:

edwards.aus

Description:

This is an example to manipulate Adobe Reader 10 functions with DDE Communication method on Adobe Reader application.

Discussion:

This example contains the following functions: 1. Shell to start Adobe Reader application 2. Initiating DDE connection with adobe reader 3. Opening Document (with DocOpen/FileOpen) 4. Go to a specified page in a document 5. Print file using with the specified printer 6. Close the document The functions were tested on Windows 7 using Adobe Acrobat Reader R10 in Microsoft Excel 2010. In practice, this code can be used to manipulate PDF file when Adobe Reader is installed (note NOT Adobe Acrobat X). Adobe Reader has limited functionality as compared to Adobe Acrobat which is listed at the following location: http://acrobat-dde.nnn2.com/

Code:

instructions for use

			Public Function AdobeTest() As Long 
    Dim lng As Long 'Return from shell
    Dim chanNo As Long 'DDE Channel number for Acrobat Reader
    Dim strPath As String 'String Path for the file
    Dim printName As String 'Printing the file
    Dim driverName As String 'Name of the printer driver
     
     'Name of the printer driver
    driverName = """""" 
     
     'Name of the printer
    printName = """PrimoPDF""" 
     
     'String file path
    strPath = """C:bibentries.pdf""" 
     
     'Open Adobe Reader
    lng = Shell("C:Program FilesAdobeReader 10.0ReaderAcroRd32.exe", vbNormalFocus) 
     
    chanNo = DDEInitiate("AcroViewR10", "Control") 
     
     'File is opened and displayed with DocOpen
    DDEExecute chanNo, "[DocOpen(" & strPath & ")]" 
     
     'File is opened and displayed with FileOpen
    DDEExecute chanNo, "[FileOpen(" & strPath & ")]" 
     
     'Go to page 2 of the document
    DDEExecute chanNo, "[DocGoTo(" & strPath & ", 2)]" 
     
     'Print the file
    DDEExecute chanNo, "[FilePrintTo(" & strPath & "," & printName & "," & driverName & ")]" 
     
     'Close PDF file
    DDEExecute chanNo, "[DocClose(" & strPath & ")]" 
     
     'Close Adobe Reader
    DDEExecute chanNo, "[AppExit( )]" 
End Function

How to use:

Before executing the code, modify the variables driverName (Name of the driver - usually not required for printing), printName (name of the printer) and strPath (Path to PDF file).
Change to "AcroView" if Adobe Reader 10 not installed in line "chanNo = DDEInitiate("AcroViewR10", "Control")"
Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

Copy attached pdf file in the C: drive.
Update the strPath and the printName accordingly
Run the code in debug mode by pressing F8 button in VBE

Sample File:

vbacrobat.zip 69.93KB

Approved by Jacob Hilderbrand

This entry has been viewed 38 times.