When I run a vba script to print excel spreadsheet data, PDF creator attempts to link to the internet. If there is no internet connection, it sometimes hangs and never prints. I then have to stop the process manually.
Note: I am using XP Pro SP3, and Office 2003. I have used various versions of PDFCreator, currently 1.5.1, but some of the isues were seen on 1.5.0 and earlier.
My script is slightly different to that on the examples delivered with recent versions, but not ( I believe) seriously so. Perhaps I am a bit light on error detection. I use the following code:
Sub PrintToPDF(ByVal Print_File_Name As String, ByVal Print_Range_Name As String)
'Author : Ken Puls (http://www.excelguru.ca/)
'Adapted by Peter Griffin
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for either early bind with reference to PDFCreator, or late bind, no references req'd
Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
Dim DefaultPrinter As String
Dim AddressAreaToPrint As Variant
' Save the currently active printer name
DefaultPrinter = ActivePrinter
'/// Change the output file name here! ///
sPDFName = Print_File_Name & ".pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator & "Results" & Application.PathSeparator
'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
' Late Binding
'Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
' Early Binding by setting References to include PDFCreator
Set pdfjob = New PDFCreator.clsPDFCreator
With pdfjob
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") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Print the required range to PDF
AddressAreaToPrint = CVar("=" & ActiveSheet.Name & "!" & Range(Print_Range_Name).Address)
ActiveSheet.PageSetup.PrintArea = "" 'defensive programming
ActiveSheet.PageSetup.PrintArea = AddressAreaToPrint
ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
' Revert to previous printer
ActivePrinter = DefaultPrinter
End Sub