PDA

View Full Version : Access 2007 Printing to PDFCreator



ExcelOldTimer
2011-05-23, 09:59 PM
Hello!

I'm attempting to use the VBA code supplied on this page: http://www.excelguru.ca/node/68 and called: "PrintAccessReportTOPDF_Early."

I'm getting the error <Can't initialize PDFCreator> when I Run. I did follow the instructions for setting it to early bind.

Wondering if my problem is more basic & how to fix it. My copy of PDFCreator is located on my local C: drive, but my database is stored on a network drive. Please note that my company blocks all attempts to download material, so I can't download another copy of PDFCreator to be located in the same place as my dataset.

Can I point to my C: drive from within the code you supplied?

I want to test to see if this works so I can drop it in a piece of code that will let me loop through a list of criteria and print individual reports for each of several different entities (contained in a field on the report). Does this sound workable? I'm thinking it should be, but not sure if it will trip over itself.

Thanks so much for any help you can provide!!

Ken Puls
2011-05-25, 07:37 AM
Hi there, and welcome to the forum!

The location of the PDFCreator program files are fine on C:\ drive. So long as you've checked that library in Tools-->References, you're good to go there. In fact, I'd advocate very strongly to leave them there and not put them in the same folder as your database.

I'm curious... do you always get this error, or does it work the first time you start your PC then fail thereafter? Can you try using taskmanager to kill off the PDFCreator instance and then try running the routine? Generally that error shows up when PDFCreator is already running...

Also, what version of Office and operating system are you using?

ExcelOldTimer
2011-05-25, 03:02 PM
Hi Ken! Thanks...Glad to have found you. :)

I do have the Tools -> Refs checked for PDFCreator and it has the appropriate path. Yesterday, before receiving your reply (for which, thank you very much!), I copied my database onto my C: drive to test (can't leave it there, we must store all data on the servers due to HIPAA security and backup procedures). No longer received that error, but a new problem popped up.

PDFCreator is initialized, the window showing all 31 pages being sent pops-up, and I see the pop-up system message indicating that a report was "sent to printer xxxxx", but then it hangs up. My System Tray shows the PDFCr icon with a red dot on it, telling me it has stopped. I turned on Logging in PDFCreator and received the following output:

5/24/2011 9:37:32 AM: PDFCreator Program Start
5/24/2011 9:37:32 AM: Windowsversion: Windows XP 5.1 Build 2600 (Service Pack 3) [TerminalServer WinXP WinXPProEdition IsWinXPPlus IsWinXPProEdition]
5/24/2011 9:37:32 AM: InstalledAsServer: False
5/24/2011 9:37:32 AM: MyAppData: C:\Documents and Settings\kim\Application Data

The status in PDFCr is "Printer Stop" when you look at the menu, of course, validating the red dot. I can see that it did capture the report name and a path within PDFCr under the Auto-Save options, but no report was actually generated. There are no documents left in the queue.

[Now please forgive me, I'm sqeaky-new to VB and it's been 20 years since I wrote any kind of Access code!! ...I live & breathe Excel, but we've simply outgrown it :( ...time to re-learn Access!]

When I look at the VB code, it appears that it has stopped here (below) and appears to be looping (multiple stopped instances of PDFCr are open). The reason I think this is the spot, yesterday there was a handy-dandy yellow arrow pointing directly to this spot. Today I don't recall how I got that brilliant yellow beacon to appear. :)

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

Currently using Office 2007 in Windows XP Pro 2002 SP3 environment, as you can see in the logfile. It is possible that PDFCr had been running yesterday as I PDF everything I generate for transmission to outside entities. However, as above, simply moving the database to my C: drive eliminated that error.

I have made no modifications to the code, simply added it to my database and clicked Run. Perhaps there is something I need to do? Ultimately, as noted above, I need to generate 23 individual PDF documents to be sent to different offices weekly for one report, monthly for another, so I'd LOVE for this to work!

Any thoughts?

Thanks so much!!
Kim

Ken Puls
2011-05-26, 12:12 AM
Hi Kim,

I'll dig out a couple of replacement code pieces for you tonight, just don't have time right at the moment. Hopefully they'll fix you up. :)

Ken Puls
2011-05-26, 05:05 AM
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:

Sub PrintAccessReportToPDF_Early()
'Author : Ken Puls ( www.excelguru.ca (http://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...

denisu
2011-11-30, 12:30 PM
Hi Ken I tried to use this code some time ago and every second time through worked perfectly. I ignored PDF Creator for a while and just came back to it, and I found the issue for me, and it may help someone else.
I changed the Printer loop in the middle, because for multiple calls it was just not picking up the PDFCreator printer everytime. I changed my code this way

Dim sTEMP As String
For lPrinter = gc_0 To Application.Printers.Count
Set Application.Printer = Application.Printers(lPrinter)
Set prtDefault = Application.Printer
sTEMP = Left(prtDefault.DeviceName, gc_10)

Select Case sTEMP
Case Is = "PDFCreator"
lPrinterPDF = lPrinter
Case Is = sPrinterName
lPrinterCurrent = lPrinter
Case Else
'do nothing
End Select
Next lPrinter
On Error GoTo 0
If Left(sPrinterName, gc_10) = sTEMP Then lPrinterCurrent = lPrinterPDF
'Change the default printer
Set Application.Printer = Application.Printers(lPrinterPDF)
Set prtDefault = Application.Printer

Putting the test for the "PDFCreator" printer first avoided the issue that the PDFCreator Printer can be the default printer!

Ken Puls
2011-11-30, 09:49 PM
Cool. Glad you got it sorted, and thanks for posting back about it. :)

cordelia
2014-08-25, 04:49 PM
Thanks for the tips and for post the examples, I was needing this information.