Hi Kim,
I've pulled in some of the techniques that I've found more successful in closing Excel PDF routines and merged them into the article you referenced. I've checked that it compiles, but I haven't done any testing on it, so be aware that there might be some bugs still. It shouldn't break anything in the database though. (If you're at all nervous, however, you should consider making a backup before testing.)
Here's the revised code:
Code:
Sub PrintAccessReportToPDF_Early()
'Author : Ken Puls ( www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/ )
' Designed for early bind, set reference to PDFCreator
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim sPrinterName As String
Dim sReportName As String
Dim lPrinters As Long
Dim lPrinterCurrent As Long
Dim lPrinterPDF As Long
Dim prtDefault As Printer
Dim bRestart As Boolean
'/// Change the report and output file name here! ///
sReportName = "Chart of Accounts"
sPDFName = sReportName & ".pdf"
sPDFPath = Application.CurrentProject.Path & "\"
'Activate error handling
On Error GoTo EarlyExit
'Check if PDFCreator is already running and attempt to kill the process if so
Do
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running. Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
DoEvents
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False
'Resolve index number of printers to allow changing and preserving
sPrinterName = Application.Printer.DeviceName
On Error Resume Next
For lPrinters = 0 To Application.Printers.Count
Set Application.Printer = Application.Printers(lPrinters)
Set prtDefault = Application.Printer
Select Case prtDefault.DeviceName
Case Is = sPrinterName
lPrinterCurrent = lPrinters
Case Is = "PDFCreator"
lPrinterPDF = lPrinters
Case Else
'do nothing
End Select
Next lPrinters
On Error GoTo EarlyExit
'Change the default printer
Set Application.Printer = Application.Printers(lPrinterPDF)
Set prtDefault = Application.Printer
'Start PFF Creator
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 document to PDF
DoCmd.OpenReport (sReportName)
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName
Cleanup:
'Release objects and terminate PDFCreator
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
'Reset the (original) default printer and release PDF Creator
Set Application.Printer = Application.Printers(lPrinterCurrent)
Set pdfjob = Nothing
Exit Sub
EarlyExit:
'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
End Sub
Let me know how it works out...
Access 2007 Printing to PDFCreator
Reply With Quote
...time to re-learn Access!] 
Bookmarks