Access 2007 Printing to PDFCreator

ExcelOldTimer

New member
Joined
May 23, 2011
Messages
2
Reaction score
0
Points
0
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!!
 
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?
 
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
 
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. :)
 
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 ( [URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from [URL]http://sourceforge.net/projects/pdfcreator/[/URL] )
'   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...
 
Update to code on Printeing to PDFCreator from MS Access VBA

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
Code:
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!
 
Back
Top