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.

 

[mole999]

I'm just wondering if the inbuilt ability to produce pdf from excel now is part of the cause, I used to have to port to acrobat to do pdf in the past

 

[JytteC]

that's why I tried both ways, built-in and otherwise, both with same bad result, and I just don't get it ???????

 

[B___P]

Did you change PDFCreator's settings too?
Try something in PDF output tag "compression" or uncheck "include all characters".
I faced with a similar issue and think depends on the graphic excel sends to printer.

Hope this will help you

Bye

 

[mole999]

any hard disk changes between then and now, maybe a new chunk size ?

 

[JytteC]

It's a brand new PC, so yes many changes. But I get 'small size' when I do the same thing manually.
Btw, in the 'exportas' thing, I tried QualityStandard as well as QualityMinimum, with exact same result.

Is there a 'chunk size' somewhere inside Excel that I may find and change??

 

[JytteC]

I just took the ' includedocproperties:=True ' out, and that shaved it down to 228 KB, but still too big.

 

[B___P]

My issue is even stranger:
About 2000 printouts only the first has standard size, about 22 K, the others about 256K.
Starting from the second one, the first huge, speed slows down more than proportional.
May be an issue with the internal renderer.

 

[mole999]

chunk size was me referring to the physical harddisk sectors > https://en.wikipedia.org/wiki/Advanced_Format

 

[JytteC]

mole999, got it no can't have anything to do with that

B-P, well I'm sort of glad I'm not alone.