Print To PDF Using Microsoft's PDF/XPS Add-in

[swastik]

Thank you very much for sharing how to Print To PDF Using Microsoft's PDF/XPS Add-in. It is very useful workout for me.
I would like to know if there is a way to print not editable PDF copy.

[Ken Puls]

Not with Excel's built in tools that I'm aware.

You may have a better time trying to make that happen with PDFCreator, but there are a vast amount of tools on the market now who's entire purpose is to pull content out from a PDF. It's not as secure as it once was...

[swastik]

Not with Excel's built in tools that I'm aware.

You may have a better time trying to make that happen with PDFCreator, but there are a vast amount of tools on the market now who's entire purpose is to pull content out from a PDF. It's not as secure as it once was...

Thank you Sir for your response. Since last two years i was using your Sub PrintToPDF_Early() to print invoices with scan signature, organisational Logo and post to our clients. A copy of our invoice is attached for your easy understanding. It was working fine.

Later I started using Sub PrintToPDF_WithSecurity() to protect the scan signature logo etc appearing on our invoice. That also worked fine for me. However, recently i started getting the error message "Can't initialize PDFCreator". I tried with all your suggestions but could'nt resolve. I tried downloading the new version of PDFCreator (Download PDFCreator-1_3_2_setup.exe (17.8 MB)) from sourceforge.net/projects/pdfcreator/files/. In this process instead of resolving my problem my computer got infected with some malwares. Therefore, I switched over to your "Print To PDF Using Microsoft's PDF/XPS Add-in."

I would love to go back to your Sub PrintToPDF_WithSecurity(). The key objective is to protect someone from easyly manupulate with signature, logo, emblem etc. I would look forward to your suggestion.

[Ken Puls]

Hi there, Swastik,

Sorry to hear you had issues with the PDFCreator download. I've never heard of them having viruses in their downloads.

With regards to the issues with the "Can't initialize PDFCreator" message, I've seen this happen before. The most frequent cause that I've seen on PC's where it has been working fine is that after some software gets installed PDFCreator starts failing. I have no idea what sofware though... could be a windows update, a new program that runs in memory, could be anything. My suspicion is that it occurs when a new service is registered in Windows that takes just a little bit too many resources that PDFCreator was needing. Typically it hangs the PDFCreator process, and that prevents it from loading again.

Regardless, we can deal with it. At worst case we force a pause into the code, but I've put a fair effort into trying to work around that as I just don't like that concept at all.

The code below is a revised version of the Using PDFCreator with security options set article. It's got a revised method that checks if PDFCreator is hung up and kills it off if so.

 Sub PrintToPDF_WithSecurity()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator WITH SECURITY
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator
    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim bRestart As Boolean
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim sMasterPass As String
    Dim sUserPass As String

    '/// Change the output file names and passwords (if security req'd)! ///
    sPDFName = "testPDF.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    sMasterPass = "master"
    sUserPass = "letmein"

    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    '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

    With pdfjob
        'Set details on where to save file to, and flag it automatic
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        
        'The following are required to set security of any kind
        .cOption("PDFUseSecurity") = 1
        .cOption("PDFOwnerPass") = 1
        .cOption("PDFOwnerPasswordString") = sMasterPass

        'To set individual security options
        .cOption("PDFDisallowCopy") = 1
        .cOption("PDFDisallowModifyContents") = 1
        .cOption("PDFDisallowPrinting") = 1

        'To force a user to enter a password before opening
        .cOption("PDFUserPass") = 1
        .cOption("PDFUserPasswordString") = sUserPass

        'To change to High encryption
        .cOption("PDFHighEncryption") = 1
    
        'Get ready for the print job
        .cClearCache
    End With

    'Print the document to PDF
    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 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
    Application.ScreenUpdating = True
    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

If that doesn't solve it, let me know and we'll look at putting some pauses in.

[swastik]

Dear Ken,

Thank you so much for the solution. It is working fine.

Swastik

[ipisors]

sorry to butt in. Wouldn't you rather just use Excel's VBA built in method: ExportAsFixedFormat?

It allows you (by vba) to turn any excel sheet into a PDF which can't be edited. And it's one line of code..

no special printer, no program, no nothing....

[Ken Puls]

sorry to butt in. Wouldn't you rather just use Excel's VBA built in method: ExportAsFixedFormat?

In this case no. The built in Microsoft tools won't allow you to set security options on the PDF.

You're correct though, in many cases the PDF code is no longer required as Microsoft added PDF ability in 2007. (It did not exist prior, which is when these techniques were first implemented.)

[ipisors]

that was one thing that softened the blow when i learned 2007 after 2003. many things I think we all sat there and cried in our beer for a while (where are the menu's and why can't we [as easily] use Alt keys anymore) LOL.......Then I was like Oh, free PDF creation, not bad!

but yeah i see what you mean. no security just a rough PDF.

In this case no. The built in Microsoft tools won't allow you to set security options on the PDF.

You're correct though, in many cases the PDF code is no longer required as Microsoft added PDF ability in 2007. (It did not exist prior, which is when these techniques were first implemented.)

[Ken Puls]

...many things I think we all sat there and cried in our beer for a while (where are the menu's and why can't we [as easily] use Alt keys anymore) LOL.......Then I was like Oh, free PDF creation, not bad!

LOL! I hear you. The ribbon really did distract from the good feature adds to the 2007 product. (Conditional format overhaul, data bars, sparklines, tables, PDF creation, trusted locations and more.)