Results 1 to 8 of 8

Thread: Access 2007 Printing to PDFCreator

  1. #1

    Question Access 2007 Printing to PDFCreator



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Neophyte denisu's Avatar
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    1
    Articles
    0

    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!

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Cool. Glad you got it sorted, and thanks for posting back about it.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Thanks for the tips and for post the examples, I was needing this information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •