Creating a PDF from Excel typically requires printing to the “Adobe PDF” printer, selecting the location and file name to save the .pdf as, and finally getting the newly created PDF opened in Acrobat. Automating this is not very straight-forward, and took quite a while to get “right”.
Background
There is no direct COM API that can be used to tell Acrobat to “convert this sheet to PDF please”, and all the solutions I’ve found on the net have been missing pieces of the puzzle (i.e. lots of hardcoded values, only works on the computer they were made on, or requires you to do manual steps before you can start.)
The most common manual step is that you have to uncheck “Don’t send fonts to Adobe PDF”, or as it’s called in recent versions of Acrobat:
Adobe PDF setting: Use system fonts
The problem here is that this setting is in no way persistent, often resetting to its checked state – and there is no registry setting or .ini file setting to force it unchecked.
What you’ll need
As usual, these are the specific versions I use – other versions should work just as well.
- Excel 2003
- Adobe Acrobat 8
- A printer with a PostScript driver (you can also install a dummy printer if you don’t have a “real” printer)
The code
Some of this code is based on this PCreview.co.uk forums post (I can’t remember if this was my original source, but it’s the closest I can find)
You’ll need the file cAcroDist.cls, a class that handles the Distiller events – either import the linked file, or create a new class in VBA and paste the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | Option Explicit Public WithEvents odist As PdfDistiller Attribute odist.VB_VarHelpID = -1 Dim StartTime As Date Private Sub Class_Initialize() Set odist = New PdfDistiller End Sub Private Sub odist_OnJobDone(ByVal strInputPostScript As String, ByVal strOutputPDF As String) Kill strInputPostScript ' deletes the input postscript, since it's not needed after PDF is made End Sub Private Sub odist_OnJobFail(ByVal strInputPostScript As String, ByVal strOutputPDF As String) End Sub Private Sub odist_OnJobStart(ByVal strInputPostScript As String, ByVal strOutputPDF As String) End Sub |
On to the PDF producing code – either use the code below, or import this handy pre-made module: PrintToPDF.bas (note that this needs some manual changes).
In a new module, create the following Sub:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | Sub PrintToPDF(sPDFFileName, SheetName) Dim sPSFileName As String 'Name of PS to be created Dim sJobOptions As String Dim sCurrentPrinter As String 'Same current printer choice to resume at end Dim sPDFVersionAndPort As String 'Version of Adobe Dim sDummyPrinter As String ' Need a dummy printer to produce the PS Dim appDist As cAcroDist Set appDist = New cAcroDist sCurrentPrinter = Application.ActivePrinter 'Save the currently active printer sDummyPrinter = NetworkPrinter("Dummy Printer Name") ' Change this to match an installed PS-capable printer driver Application.ActivePrinter = sDummyPrinter sPSFileName = ThisWorkbook.Path & ThisWorkbook.Name & ".ps" 'Name of PS file ThisWorkbook.Sheets(SheetName).PrintOut ActivePrinter:=sDummyPrinter, _ PrintToFile:=True, PrToFileName:=sPSFileName 'Prints to PS Call appDist.odist.FileToPDF(sPSFileName, sPDFFileName, sJobOptions) 'Creates PDF On Error Resume Next Kill sPSFileName 'Removes PS On Error GoTo 0 Application.ActivePrinter = sCurrentPrinter 'Change back to the original printer End Sub |
This procedure finds a specifically named printer, uses its driver to produce a PostScript file, then uses Acrobat Distiller to convert the .ps to .pdf. You will need to add a reference to Acrobat Distiller manually in the VBA editor.
Finally, we need the NetworkPrinter function, which locates which port a printer is on – you CAN hardcode sDummyPrinter to something like “HP Deskjet on Ne03:”, but that will only work on computers that have the printer on that exact port. The NetworkPrinter function – slightly modified solves this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | Dim Prt_On As String Function NetworkPrinter(ByVal myprinter As String) On Error Resume Next Dim NetWork As Variant Dim X As Integer Prt_On = " On " '/// Define NetWork Array \\\ NetWork = Array("Ne00:", "Ne01:", "Ne02:", "Ne03:", "Ne04:", _ "Ne05:", "Ne06:", "Ne07:", "Ne08:", _ "Ne09:", "Ne10:", "Ne11:", "Ne12:", _ "Ne13:", "Ne14:", "Ne15:", "Ne16:", _ "LPT1:", "LPT2:", "File:", "SMC100:", _ "XPSPort:") 'Setup printer to Print X = 0 TryAgain: On Error Resume Next 'Printer Application.ActivePrinter = myprinter & Prt_On & NetWork(X) If Err.Number <> 0 And X < UBound(NetWork) Then X = X + 1 GoTo TryAgain ElseIf Err.Number <> 0 And X > UBound(NetWork) - 1 Then GoTo PrtError End If On Error GoTo 0 NetworkPrinter = myprinter & Prt_On & NetWork(X) errorExit: Exit Function PrtError: 'no printer found NetworkPrinter = "" Resume errorExit End Function |
This is also defined in PrintToPDF.bas linked above.
Example use
To convert the “Report” sheet in the currently active workbook to a PDF, use this code:
1 | PrintToPDF "C:\MyReport.pdf", "Report" |
Troubleshooting
No PDF produced
If the printer you use for conversion doesn’t support PostScript, you’ll only end up with a text file called MyReport.txt (following the example above) containing an error message. Change to a different printer to avoid this – alternatively install a fake printer (see next section) to get a compatible driver.
PDF produced has paper size “letter”, regardless of settings
I’m still working on fixing that one…
Installing a fake printer
From Start -> Printers and Faxes -> Add a printer
Installing a fake printer, step 1/6
Installing a fake printer, step 2/6
Installing a fake printer, step 3/6
Installing a fake printer, step 4/6
Installing a fake printer, step 5/6
Installing a fake printer, step 6/6
In short, add a new local printer on LPT1: – don’t search for PnP printers – using a random printer driver with PS in the name (I used the HP Color LaserJet PS – I’m not sure if a non-color printer would work, too), and don’t print a test page. It would probably be possible to share this printer with a network and have everyone install it to have a unified way of printing on a set of computers, but I haven’t tested this.
Hi,
Thanks for the article, I think it may have solved a very big problem for us. However, I have a problem getting it to work, unfortunately. I have set up a dummy printer (call it “PS_Printer”) on a dummy port (“PS_Port”), and used the following code:
ThisWorkbook.Sheets(SheetName).ActivePrinter = “PS_Printer”
ThisWorkbook.Sheets(SheetName).PrintOut(FileName:=strFilename, Copies:=1, PrintToFile:=True, OutputFileName:=sOutputFilename)
..but the application still tries to print to the default printer. Have I missed something? I have tried using the dummy printer from Word, and it seems to work just fine, so I’m wondering if the code is somehow “missing” the dummy printer.
Any help would be very gratefully accepted!
You need to set the printer using the NetworkPrinter function specified above:
Application.ActivePrinter = NetworkPrinter(“PS_Printer”)
The NetworkPrinter function tries to detect which port the printer is on and set the printer string accordingly. If the string doesn’t resolve to a proper printer (“PS_Printer on PS_Port”) it will use the default printer.
Alternatively you can of course set .ActivePrinter = “PS_Printer on PS_Port” directly – the NetworkPrinter is more for when you use a physical printer which may or may not be on the same port on each computer in a network.
You, sir, are a champion among men.