macro print to pdf.... SIZE CONUMDRUM

[JytteC]

I used to be on Excel 2003 (lovely that), and I used to print out invoices etc to a pdf file with this macro:

Set pdfjob = CreateObject("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
    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
    
    pdfjob.cClose
    '
    Set pdfjob = Nothing

the operative part being simply:

 ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

And this produced nice little pdf-files at the size of 22 KB, used it for years and years, never a problem.

Well, then I just switched to Excel 2016 (NOT lovely), and the above does not work, so made this instead:

 ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator", PrintToFile:=True, PrToFileName:=sPDFPath & sPDFName

simple, works too, BUT, now I get a pdf file of 245 KB !! And for several good reasons this is way too big for my use.

now, if I do the exact same thing manually, namely go to print, use pdfcreator, and save the file, I get a nice 22 KB file as before.
Why in God's name won't it do it from macro then????

So I decided to take another approach, and made this:

ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=sPDFPath & sPDFName, _
        Quality:=xlQualityMinimum, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

also does the job, but again I get a huge 245 KB file.

Anybody have any bright ideas??? I cannot use files that big, and having to do this manually (it's also a matter of getting the exact right filename and place to put it) is a huge drag.

 

[JytteC]

Well, I've tried experimenting with batch file for PDFCreator, and that is totally useless. PDFCReator has ONE parameter (!!!) meaning you have no control over destination, it will only allow you to print a file with it's 'same name', which again means I'd have to first create a file with the correct name, then pdfprint it. Useless. On top of that PDFCreator adds an 'm' to my filename (???), AND I'd have to create a printer for each type of file (because it would have to autosave, and direct to certain folder).
I've always liked PDFCreator, because my macros worked, but now.... GRRRRRRRRRRRRR

 

[JytteC]

Added to which.... when trying this (command print) out on a test file, it created a file. When trying it out on one of my 'real files' (which are big with multiple sheets) it crashed. SIGH

 

[mole999]

2016 is a different beast to 2003, it went xml on us

 

[JytteC]

VERY DIFFERENT. Any and all people even remotely connected to Microsoft are in my prayers every night, and NOT in a good way

 

[JytteC]

guys, I'm now thinking that this problem may not lie in the code but somewhere else....

re-wrote my code for the test sheet, looks like this (one that always worked):

Sub PrintPDF() ' Save selected as PDF (and open it)
'
    Dim pdfjob As Object
    Dim sPDFName As String
    Dim sPDFPath As String
    '
    Dim iret As Integer
    Dim strTitle As String
    Dim strPromt As String
    Dim oldfile As String
    '
    'input file folder and name from FILE
    sPDFName = "Test.pdf"
    sPDFPath = "C:\TEMP\"
    '
    oldfile = "C:\TEMP\Test.pdf"
    '
    'check if file alrady exists
    If Len(Dir(oldfile)) Then
     strTitle = "Warning:              "
     strPrompt = "The file already exists. Do you want to delete it?"
     iret = MsgBox(strPrompt, vbOKCancel, strTitle)
     ' Check pressed button
       If iret = vbCancel Then
         Exit Sub
       Else
         Kill oldfile
       End If
    Else
     'ok
    End If
    '



    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
    '
    Set pdfjob = CreateObject("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
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator on Ne01:"
    '
    '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
    
    pdfjob.cClose
    '
    Set pdfjob = Nothing
    '
    ' Open the pdf file
    MyPath = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
    myfile = "C:\TEMP\Test.pdf"
    Shell MyPath & " " & myfile, vbNormalFocus
    '
End Sub

and it chokes on this: Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

and I get this error message:

run-time error '429':
ActiveX component can't create object

so, I have some other problem???

 

[mole999]

at the top of the module do you have option explicit ?

also found this syntax

Set pdfjob = New PDFCreator.clsPDFCreator

 

[Kenneth Hobson]

Use /code rather than \code as the closing tag.

Obviously, that class no longer exists in your registry. Use Regedit to Find class names when in doubt.

You must have uninstalled the v1 program. Try using the early binding so that you don't have to worry about scripting the correct object using the class name in CreateObject().

I installed the latest pdfcreator v2.2.2 and will see how my early binding code works out.

 

[JytteC]

I am BEYOND FURIOUS !!

Just finished a chat with a tech support (so called) from Microsoft. This was the last in a long row of chats to try to solve my MANY problems with this new system. This one had me spitting fire at the end of it, and I had to end the chat before I started calling her not-so-nice names. I told her I was getting an error message (and gave her the specific) and asked if there might be a driver or something I should re-install. I have seen 'fixes' when googling, but a) I don't know how old that is and if it will work on Win 10, or if this is an inherent Win 10 problem b)I am not comfortable fiddling in regedit. SHE told me I had a corrupted system, caused by a faulty installation of Windows, and if I'd just pay up an extra 149 bucks, she might be able to help me. EXCUSE ME? Alternately I should go to the producer (Dell) and have them help me, as they installed it. EXCUSE ME? So while I was chatting with her, I ran the same test on the other 2 PCs (both brand new and both on the same win 10 and Office 2016), and with the exact same result. Told her I thought it highly improbable that I would get THREE computers all with the exact same faulty installation. She didn't seem to think so, and insisted I pay extra for any help! Even now I have a hard time typing without using foul language!

At any rate: 'Set pdfjob = New PDFCreator.clsPDFCreator' gives same result

no, I don't have 'option explicit' at the top or anywhere, not sure how to use that?

 

[JytteC]

Kenneth, sorry about the typo
uninstalled the v1 program? you mean PDFCreator?
and I don't know how to use regedit
I also am quite upset and have a hard time thinking straight

 

[mole999]

option explicit just goes at the TOP of every other piece on that sheet VBA. picks up on mistyped names and references when compiling (I don't know why or how)

on the other side, MS will probably say they provide the software and as the PDF creator is not of their desin then its an add on, and that that liability exists with that software writer (me buying mini tyres to fit my truck, the truck company couldn't be liable)