KHKonsulting LLC

This is from the category “Frequently Asked Questions” – this time how to programmatically specify an output filename when printing to the Adobe PDF printer. As you may know, the “Adobe PDF” printer allows you to create PDF files from any application on a Windows system that can print. All you need to do is (that is, after you’ve installed Adobe Acrobat), select to print, then select the “Adobe PDF” printer, specify any job options, and print. Voila, a new PDF file is created – after the application asks you to specify the filename for the new PDF file. That is usually a good thing when you manually initiate the print operation, because then you know where your PDF file gets stored on the computer. However, if you want to programmatically create PDF files from your application (e.g. from an MS Office application using VBA), that step is quite annoying, and can ruin one’s day when trying to process 1000 files.

In the olden days, it was necessary to first print to a PostScript file, and then call Distiller from your program to convert that PostScript file to PDF, but for quite some time now, Adobe provides a way to specify that PDF filename by setting a registry key. The details can be found in the Acrobat SDK. Here is the link to the page that describes this registry key  process.

The registry key HKEY_CURRENT_USER\Software\Adobe\Acrobat Distiller\PrinterJobControl should already exist if you’ve printed to the Adobe PDF printer before. If it does not exist, create it before we go any further (or, just print using the Adobe PDF printer).

The documentation requires us to create a sub key – or a key value pair – where the key is the path to the application that wants to save a PDF file, and the value being the filename for the PDF file. Sounds more complicated than it actually is: To print from the WordPad application to a PDF file without begin prompted for the filename use the following key value pair:

C:\Program Files\Windows NT\Accessories\wordpad.exe = c:\MyPDFFileName.pdf

To do this in the regedit tool, navigate to the PrinterJobControl key and right-click in the pane that shows the key value pairs. Then select New>String Value – this will actually create a new string value, and will open the name up for editing – change it to C:\Program Files\Windows NT\Accessories\wordpad.exe

Once that is done, right-click on the new entry and select “Modify”. In the “Edit String” dialog set the value to c:\MyPDFFileName.pdf and click on OK.

After creating this registry key, print from WordPad and see how the file is printed without prompting for a filename. The other thing that will happen is that the key we just created gets removed. This means that this key needs to be created before every print job that needs to be saved to PDF automatically. You can see this by refreshing the view in the regedit application, but also by printing again from WordPad: This time, it will prompt for a PDF filename.

Sometimes it’s not obvious which application is actually printing – you may be running one application, but in the background it is handing control over to the application that is handling the print process. In this case, you can use the registry editor to see which application is responsible for printing. In the screen shot I’ve attached, you can see entry called “LastPDFPortFolder – wordpad.exe” – such an entry gets created every time an application prints to the Adobe PDF printer. By clearing out all sub-keys in PrinterJobControl, we can make sure we know which application was last used to print. We won’t get the full path, but just knowing the name of the executable will help to find the application.

I’ve written a small VB Script sample that will take the path to one or more Excel files on the command line, and will print these files to the default printer after setting the registry key to specify the PDF output filename.

' Set registry key to control PDF output and print an Excel
' file to PDF
' Karl Heinz Kremer - khk@khk.net - 1/18/2013 

Dim fso, exl, exlWkbk

const HKEY_CURRENT_USER = &H80000001

strComputer = "."

Set StdOut = WScript.StdOut
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Adobe\Acrobat Distiller\PrinterJobControl"

' Just in case, create the PrinterJobControl registry key -
' it should already exist
oReg.CreateKey HKEY_CURRENT_USER,strKeyPath
Set fso = CreateObject("Scripting.FileSystemObject")
Set exl = CreateObject("Excel.Application")

exl.Visible = False

If WScript.Arguments.Count = 0 Then
WScript.Quit
Else
For A = 0 To (WScript.Arguments.Count - 1)
If ((Right(WScript.Arguments.Item(A), 3) = "xls" OR Right(WScript.Arguments.Item(A), 4) = "xlsx") AND _
fso.FileExists(WScript.Arguments.Item(A))) Then

' set the registry key
dir = fso.GetParentFolderName(WScript.Arguments.Item(A))
basename = fso.GetBaseName(WScript.Arguments.Item(A))
ext = fso.GetExtensionName(WScript.Arguments.Item(A))

strValueName = "C:\Program Files\Microsoft Office\Office14\excel.exe"
strValue = dir & "\" & basename & ".pdf"
oReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

Set exlWkbk = eXL.Workbooks.Open(WScript.Arguments.Item(A))
exlwkbk.PrintOut , , , , "Adobe PDF"
exlWkbk.Close xlDoNotSaveChanges
End If
Next
End If

eXL.Quit
Set fso = Nothing
Set exl = Nothing

You can download the script here.

To run the script, provide the full path to an Excel file on the command line:

excelprint.vbs c:\Temp\Test.xlsx

Play around with the script, see if you can implement the registry changes in a different environment (e.g. VBA or a C++ application), and most importantly, have fun!