VBA TIPS
Prints the PDF files from Excel

Sometimes I'd like to print a PDF file of the Adobe Acrobat from Excel. At first, since I had no idea how to control Acrobat Reader from Excel, so I had used Internet Explorer as a relay object instead of handling Acrobat Reader directly, then displayed a print dialog using the ExecWB Method.

The following code displays a print dialog of Acrobat Reader

  • Note that you need Acrobat Reader ® installed before you can begin.
  • You need the Microsoft Internet Controls referenced in your project for this from Tools > References of VBE menu.


Example

Place the following in a standard module.

Sub Print_PDF_Test1()
'Need a reference to Microsoft Internet Controls
'for just testing:PDF files will be placed in the local server.
    Const cnsPDF_FILE As String = _
          "http://www.puremis.net/excel/cellmasters.pdf"
    Dim ObjIE As New InternetExplorer
    ObjIE.Navigate cnsPDF_FILE
    ObjIE.Visible = True
    Do While ObjIE.Busy = True: DoEvents: Loop
    'The following line shows a setup dialog of Acrobat
    ObjIE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DODEFAULT
End Sub




However, it did not fulfill my needs in that it "automatically prints out necessary number of copies" when I did not want to show the user the dialog box, or when I would like to specify the number of copies, and it stopped when the dialog was displayed in the above-mentioned method.

So I looked at the Adobe web site, though it is not officially supported but I found a Windows Command Line that can control Acrobat Reader from windows exists.

The following is a translated document quotation from the DeveloperFAQ.pdf that is available in the Japanese language part of the Adobe web site.

How can I use Acrobat ® or Adobe Reader ® from the Windows command line?

The command line is useful for some developers although that is not supported officially. A manual concerning these commands does not exist, and the following explanation is the only one. The PDF files can be displayed or printed by using Acrobat ® or Adobe Reader ® from the windows command line.

AcroRd32.exe filename — runs Adobe Reader ® then displays a file.

The command lines and the other options are as follows.



optionmeaning
/nLaunch a separate instance of the Acrobat application
/sOpen Acrobat suppressing the splash screen
/oOpen Acrobat suppressing the open file dialog
/hOpen an Acrobat invisible mode.


AcroRd32.exe /p filename — runs Adobe Reader ® then prints a file.

AcroRd32.exe /t path printername drivername portname — Executes Acrobat Reader ® and prints a file while suppressing the Acrobat print.

/t Four parameters of the option are interpreted as path, printername, drivername, and portname.(must be character strings)

  1. path — path
  2. printername — the name of a printer
  3. drivername — The name of a printer driver. It is a driver name displayed in the driver box that can be used when the property of the printer is displayed.
  4. portname — The port name of a printer.The "/" character cannot be used for portname. When this character is included, a document will be outputted to the port of the default of the printer.
In the case that Acrobat is used, please change AcroRd32.exe to Acrobat.exe in the command line.

Though I tried option /t, it seemed that the process does not terminate automatically.

I decided to leave the paper size setting to Acrobat Reader. Be sure to set following setting before execute this program.
(1)Set Page Scaling None.
(2) Tick "Choose Paper Source by PDF page size".
The size of the document comes to be selected automatically by above setting.

The following code prints 3 copies of the PDF to the default printer. In this test, Microsoft Office Document Image Writer bundled with Office 2003 is used.



Example

Place the following in a standard module.

Sub PrintingTest()
    Dim sFileName As String
    'Change here to an appropriate file name
    sFileName = "C:\V080506.pdf"
    'Prints three copies.
    PrintPDF2 sFileName, 3
    MsgBox "Data has been sent. " & vbLf & _
           "Please close the instance of Acrobat Reader after printing."
End Sub

Sub PrintPDF2(ByVal FileName As StringOptional Copies As Long = 1)
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'++ Prints the PDF files using a command line.
'++ Written by Masaru Kaji aka Colo
'++ Syntax
'++ FileName : Required String expression that specifies a file name
'++           - may include directory or folder, and drive..
'++ Copies : Optional Long. The number of copies to print.
'++          If omitted one copy is printed.
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Const PrinterName As String = """Microsoft Office Document Image Writer"""
    Const DriverName As String = """Microsoft Office Document Image Writer"""
    Const PortName As String = """Microsoft Office Document Image Writer port:"""
    Dim cnt As Long
    Set myShell = CreateObject("WScript.Shell")
    For cnt = 1 To Copies
        myShell.Run ("AcroRd32.exe /t " & FileName & " " & PrinterName & " " & DriverName & " " & PortName)
    Next
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo