This article contains code examples to print worksheets to PDF files in a picture format, including: pdf, png, jpg, bmp, pcx, tif, ps (postscript), eps and txt.
This code example is built for PDFCreator, an open source PDF writer utility. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDF Creator from Sourceforge here. Please note that this code will NOT work with Adobe Acrobat.
It should also be noted that each of the examples in this section use an Early Bind. If you are not familiar with the difference between Early and Late Binding, please read our article on Early vs Late binding.
Versions Tested:
These routines were tested successfully using PDFCreator 0.9.1, GPLGhostscript.exe download package, on Windows XP Pro (SP2). Excel versions tested include:
- Excel 2003
- Excel 2007
Code Required:
This code goes in a standard module.
Sub PrintToPDFCreator_Early()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to Output file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for early bind, set reference to PDFCreator
Dim OutputJob As PDFCreator.clsPDFCreator
Dim sOutputName As String
Dim sOutputPath As String
Dim lOutputType As Long
'/// Change the output file name and type here! ///
sOutputName = "test"
'0=PDF, 1=Png, 2=jpg, 3=bmp, 4=pcx, 5=tif, 6=ps, 7=eps, 8=txt
lOutputType = 2
sOutputPath = ActiveWorkbook.Path & Application.PathSeparator
Set OutputJob = New PDFCreator.clsPDFCreator
'Set correct filename extension
Select Case lOutputType
Case Is = 0
sOutputName = sOutputName & ".pdf"
Case Is = 1
sOutputName = sOutputName & ".png"
Case Is = 2
sOutputName = sOutputName & ".jpg"
Case Is = 3
sOutputName = sOutputName & ".bmp"
Case Is = 4
sOutputName = sOutputName & ".pcx"
Case Is = 5
sOutputName = sOutputName & ".tif"
Case Is = 6
sOutputName = sOutputName & ".ps"
Case Is = 7
sOutputName = sOutputName & ".eps"
Case Is = 8
sOutputName = sOutputName & ".txt"
End Select
'Set job defaults
With OutputJob
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sOutputPath
.cOption("AutosaveFilename") = sOutputName
.cOption("AutosaveFormat") = lOutputType
.cClearCache
End With
'Print the document to PDF
ActiveWorkbook.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until OutputJob.cCountOfPrintjobs = 1
DoEvents
Loop
OutputJob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until OutputJob.cCountOfPrintjobs = 0
DoEvents
Loop
OutputJob.cClose
Set OutputJob = Nothing
End Sub
