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]

I just see that I don't have 'permission' to edit my posts. Why on earth not?

 

[mole999]

2 to 5Mb is not uncommon for my outputs. My initial point was that excel in the past couldn't do PDF (hence Acrobat Std), and now it can.

 

[mole999]

I just see that I don't have 'permission' to edit my posts. Why on earth not?

There is a time limit applied

 

[JytteC]

I wanted to edit within seconds of posting, but no go

 

[mole999]

I wanted to edit within seconds of posting, but no go

Not sure why that happened, has it happened before ?

 

[JytteC]

I'm brand new here, since 2 days. Maybe that's why. At the bottom under 'Posting Permissions' it says 'You may not edit your posts'.

I'm so used to type-send, that fingers get ahead of brain a lot, hence the need for edit *giggle*

 

[mole999]

I'm brand new here, since 2 days. Maybe that's why. At the bottom under 'Posting Permissions' it says 'You may not edit your posts'.

I'm so used to type-send, that fingers get ahead of brain a lot, hence the need for edit *giggle*

OK, that might explain that issue. Off the wall, and I don't know, I see from your Location - Germany / Danish.
Out of curiosity, what was your language settings in your earlier machine.
I know in desktop formulas there is an issue with commas and colons depending on regional settings, is it possible that extends into the VBA and the system is just rejecting your PDF statement and doing its own thing ?

 

[JytteC]

well, I have location Germany, but format 'English UK', and keyboard setting default English US, alternately German and Danish LOL I have no problem with my settings, once it all set up to run with the appropriate dots and commas where they need to be. No problems in the vba whatsoever either (apart from the bloody printing thing). And one can always set the individual software (like Excel) to use commas and dots as one wish.

I lived in the US for so many years, and now landed in Germany (not so happy here), and it's a royal pain. I'm so used to things 'the american way'... like dates and so on. English is now my daily language. Fortunately my very old keyboard is holding up, because getting a new one here is difficult. All the PC's bought recently came with a german keyboard, which I just trashed.

 

[JytteC]

And again, I forgot to answer a question.... My previous machine made the move from the US to here, and after landing I changed it to the setting above, so the same I have now. Like I said, no problems, as far as that is concerned.

 

[JytteC]

Main problem with the location is that computers are a bit 'too smart' nowadays. Each and every time I go to some website it automatically starts talking to me in German, drives me nuts. Although I do speak german (have to here) I prefer not to.