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.

 

[B___P]

What happens to output size if you drag 5 excel files on the print monitor?

 

[JytteC]

I'm not dragging anything. If I print out manually (also only 1 file) they're as small as they should be, 22 KB (and I use PdfCreator btw). Only when printing via macro inside Excel do I have the problem with size.

 

[B___P]

I save 2000 separate excel files in a directory then my macro loops opening them one by one and printing to PDFcreator as default printer. Months ago worked perfectly creating small size PDFs. Now same action but only first one has little size.
Printing is very slow and I think there is a problem with a running process and the PDF compression doesn't work. I make the same things but probably there has been an excel update.
I am using excel 2010 and PDFcreator 1.7.2
At the moment I solved speed issue with a macro to save my sheets as PDF but still remains size issue.

I asked how works dragging 5 files because if the size is still big the problem is outside your macro and depends on the virtual printer with too many data.

Other idea: did you switch to PDF/a format?

 

[Kenneth Hobson]

Maybe there is a compression setting? Look in the settings. Excelguru Help Site - Some Notes On Developing With PDFCreator

This MsgBox may give you a clue.

Sub Test_PDFCreatorCombine1()
  PDFCreatorCombine1 "PDFCreatorCombine.pdf", "x:\pdf\"
End Sub

'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to: PDFCreator - Your OpenSource PDF Solution
Sub PDFCreatorCombine1(sPDFName As String, Optional sPDFPath As String = "")
  Dim pPDF As PDFCreator.PdfCreatorObj, q As PDFCreator.Queue
  
  Set pPDF = New PDFCreator.PdfCreatorObj  'PDFCreator.clsPDFCreator
  Dim pj As PDFCreator.PrintJob
  
  If sPDFPath = "" Then sPDFPath = Application.Path & "\"
  
  'Set all defaults
  With pj
  MsgBox pj.GetProfileSetting(1)
  
  Exit Sub
  
    .cOption("UseAutosave") = 1
    .cOption("UseAutosaveDirectory") = 1
    .cOption("AutosaveDirectory") = sPDFPath
    .cOption("AutosaveFilename") = sPDFName
    .cOption("AutosaveFormat") = 0    ' 0 = PDF
    .cClearCache
  End With
  
  'Combine all PDFs into a single file and stop the printer
  With pj
    .cCombineAll
    .cPrinterStop = False
  End With
  
  'Wait until the PDF file shows up then release the objects
  Do Until Dir(sPDFPath & sPDFName) <> ""
    DoEvents
  Loop
  
  pj.cClose
  Set pj = Nothing
End Sub

 

[JytteC]

B-P and Kenneth, it's almost 1am here, been on tech support the last couple of hours, because windows just lost my usb ports and my command prompt (for the second time in a few days). So much fun here
I'm about to fall off my chair, but tomorrow I will surely have a look at your suggestions. Kenneth that code looks much like my old one, I'll give that a go as well.

 

[JytteC]

Turned out I was wrong in part of my posts above, must have been too tired. So, the built-in pdf code makes me a pdf file, it's just HUGE.

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

As I've now realized, NO code I have tried (and I've tried anything and everything, including above) will let me print to PDFCreator. Well, it will, in as far as it creates a pdf file, only this (all of them) file is utterly useless, as it can be opened by no software.
I even went as far as re-installing an old version of PDFCreator, same result.

What on earth did they do to Office 2016?? Make sure it will print nothing but it's own pdf? All sites, including ExcelGuru, that I can find have oodles of code, only problem is they worked fine on older software/systems, but not NOW.

 

[Kenneth Hobson]

When using the early bound method, not all parts work as they should. Printing Excel Worksheets To A PDF File | Expert Zone
pdfforge | COM Interface

Installing an old version over a new might be some cause for confusion or corruption.

Have you tried manually printing to pdfcreator?

I think that the latest version is 2.2. 2.1 worked for me but on multiple pdf file merges, it was slow.

It is probably not a good idea to inlcudedocproperties using exportasfixedformat if you are trying to minimize size.

 

[JytteC]

Kenneth, I uninstalled the new before installing the old. Didn't look good on my Win 10, but worked. As it made no difference to my problem. I again uninstalled the old, and re-installed the new (vers.2.2)
PDFCreator work wonderful when I use it manually, nice small (22KB) files (just the same as I used to get via my old macros), which opens as they should with anything, like Adobe.
But getting a readable pdf file via macro won't work any more, and the useless ones I do get are also rather big.

If I could then at least figure out why the built-in fucting gives me such big files (245KB) and change it, all would be ok, but I cannot. Nor can I use such big files.

The link you gave me (thank you) is also 'old' code, just like I had, and that does not work any more (Excel 2016 on Win 10).


Thus I am at the moment forced to manually print-to-pdfcreator all the time, which also isn't workable for me.

 

[JytteC]

Oh, forgot to mention, I long since deleted 'includedocproperties', makes no difference, still 245KB.

 

[JytteC]

I'm sorry, I just can't figure out how to edit here. It's late again, and I'm tired. On the 'includedocproperties' I set it now to false, and it does shave off a few bytes, makes no difference to me, sadly.