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.
Place the following in a standard module.
'Need a reference to Microsoft Internet Controls
'for just testing:PDF files will be placed in the local server.
Const cnsPDF_FILE As String = _
Dim ObjIE As New InternetExplorer
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
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.pd
f 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.
— runs Adobe Reader ® then displays a file.
The command lines and the other options are as follows.
AcroRd32.exe /p filename
|/n||Launch a separate instance of the Acrobat application|
|/s||Open Acrobat suppressing the splash screen|
|/o||Open Acrobat suppressing the open file dialog|
|/h||Open an Acrobat invisible mode.|
— 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.
Four parameters of the option are interpreted as path, printername, drivername, and portname.(must be character strings)
- path — path
- printername — the name of a printer
- 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.
- 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.
Place the following in a standard module.
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."
Sub PrintPDF2(ByVal FileName As String, Optional Copies As Long = 1)
'++ Prints the PDF files using a command line.
'++ Written by Masaru Kaji aka Colo
'++ 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)