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]

Kenneth, what you mentioned before about dragging a number of files to printer... As I don't drag anything, I wouldn't know. I don't print entire files, but a specific part of a specific sheet in a workbook.

 

[Kenneth Hobson]

It is unfortunate that Excel's method does not reduce file size enough for you.

Sounds like you don't have acrobat, not the reader, so that path would not be viable for you.

I just installed v2.2 of pdfcreator last night on my win10 Excel 2016. If you have an Excel file that you can obfuscate and share, upload it to a shared site like dropbox and I will take a swing at it. Show in it or say what sheet or sheets printing manually is smaller than the automated code included in the XLSM file.

 

[JytteC]

Yes I have Acrobat Reader DC. I open all files with that to make sure it can be read by any and all I send files to.
Kenneth, it's very kind of you to offer to have a go, but my Excel files work '4 up at a time' and they're all very big. Obfuscating any or all of them would be near impossible.
I do have a little test file, but what I have in there at the moment to print is only giving me files of 4-5KB, too small to notice the difference in methods.

Sadly it's hard to find any real documentation on any of this that is usable for Excel 2016 on Win 10, almost everything is of older date.

 

[JytteC]

I've looked around a bit if there's an alternative to PDFCreator that might work. But I doubt it, as the problem seems to be inside Excel, so result is likely to be the exact same.

 

[Kenneth Hobson]

Acrobat is the fully licensed version. The reader is the free reader. The former has an object that you can reference to make API calls similar to javascript but in VBA. Since you don't have it, it is not a viable option as I explained.

If pdfcreator done manually makes a smaller file, then it is not Excel that causes the big file sizes. It is the settings in pdfcreator that is reducing the file size when ran manually. When ran by code, the settings are not the same apparently.

What is making the PDF files so big: embedded images, charts, etc.? I can probably make one big enough to see if a difference can be shown. Of course as image quality is reduced to reduce file size, print quality is reduced though digital viewing quality is usually not significant for most people which what most web masters like best.

 

[Kenneth Hobson]

I did a few tests with a chart.
Sizes in kb:
1. xlQualityStandard = 248
2. xlQualityMinimum = 240
3. Save As PDF = 137
4. Print to Adobe PDF = 98
5. Print to Adobe PDF (Smallest Size Property) = 84.

I will try to test in v2.2 PDFCreator dialog and code tonight.

 

[JytteC]

You're a trooper I've been trying changing printer settings around, no results. In Excel in page layout, page setup, print quality is greyed out (?).
My prints are just an invoice, it does have a small logo on it, but nothing else fancy. I can see no reason for exportas to get this big. Like I said, printing manually from PDFCreator makes only a 22KB file.
I've been scouring through the settings in PDFCreator again and again, and can't find anything that I might change that would make a difference.
Now If I could only access my old PC to see what setup I might have had, but that's no longer an option.

 

[JytteC]

Now, if I could only figure out how to edit a post here, sigh. Wanted to say, my PDFCreator printer is set up to 600dpi (producing the 22KB file)

 

[JytteC]

I copied an invoice over to test sheet (values only), also copied the logo over. When I macro print to pdf from the test sheet I get a file of only 33KB, granted I don't have all the fancy frames and formatting in there.
When I print this test sheet manually to PDFCreator I get a file of 16KB.

The part of the 'real' sheet that produces an invoice is obviously filled with formulas, merged cells, border etc.

 

[JytteC]

btw, mole999... if you are printing out entire files (seems like your are) to pdf, you should be able to run those off a batch file instead, giving you the small files you need, and the speed too.

I've been trying to think of a workaround for me like that, but as I'm only printing part of one particular sheet in a workbook, I'd first have to save that as a temporary whatever file, then batch it, then delete the temporary file. That's clumsy, time consuming, and doesn't really seem workable for me either.