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" |
Read the rest of this entry »
Recent Comments