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.

 

[Kenneth Hobson]

Set the Option in well, VBE's menus Tools > Options... > Require Variable Declaration > OK. I like to add the debug menu's Compile to the toolbar as well. I normally set tab to 2 spaces rather than the default 5 spaces. Other options can be set to suit as well.

Not sure what you would call Microsoft for. It would certainly not be the missing class issue.

As I said, I will check out the early bound method to see if that will suffice since PDFCreator v2.2.2 has removed the old class. From what you say, it sounds like there might be an issue there as well.

 

[JytteC]

YEEEHAAAAAAAAAAAA it works now

I lookded in regedit and didn't find anything. Un-installed pdfcreator 2.2. Installed the old pdfcreator 1.3 I used to use. Now I did see it in regedit.
Ran my old code (well had to change the new path for adobe)... AND IT WORKS. I get the most beautiful pdf file and it's 26KB

I did try reinstalling the old one before without result, but maybe I did something wrong then, I don't know.

I AM SO HAPPY !!!

THANK YOU SO MUCH both of you for hanging in there with me, and pointing me in the right direction
If I could send you flowers or something I surely would.
Rest assured you are forever in my good-book

 

[mole999]

now take the rest of the weekend off

 

[JytteC]

well, no, reason for my many talks with microsoft is that Excel 2016 (I never use anything else, so wouldn't know about the rest of the package) keeps crashing, and crashing, and crashing....
It's just wonderful when you're on the phone with a customer, and down goes all my files, and I have to tell them 'sorry, my system crashed, I'll have to call you back when it's back up'.....

 

[JytteC]

now take the rest of the weekend off

OH I WISH, now onto the other problems I have LOL

 

[JytteC]

hmmmmm, I'm not sure if this is just brain-dust, but.... when I went into regedit to search for pdf, it asked me for permission (although I run as administrator)... and I went in there
then did the reinstall of v1.3.... then checked to see if pdfcreator was there and it was.....
I'm just thinking that me going into regedit sort of 'opened it up', so the new install then created the class it is supposed to?

 

[mole999]

majority of programmes need to be installed as administrator log on (which in one way is good), antivirus can be a nuisance if it quietly blocks something, and unfortunately most companies just have their file called setup, and who knows if its the one you are expecting to run

 

[JytteC]

Kenneth and mole, you both told me that you run Excel 2016 on Win 10, right? And without problems?

I know this is probably gonna sound stupid, but may I ask if you use it a LOT? Like, running 5-6 or more workbooks open at a time, one of the files being as big as 10MB, jumping back and forth between the workbooks. ?
My husband on the other pc (all same) has not had any crashes, but he only opens 5 workbooks (one of them being the 10MB one), and he just sort of LOOKS at them (for inquiry), he doesn't actually WORK them.
Whereas I open the same files, plus on and off a few more, jump back and forth, print, save files, send to email, etc etc, and I also work a LOT faster than he does (giggling). I also jump to other windows like email and browser and what not in between, and often times (usually when real busy) graphics gets 'off', like I'll get a page up with only half of it showing, and in the middle of things Excel then just freezes, and there's nothing to be done other than shooting it down via task manager.

It just feels like Excel is 'on it's limit', but for crying out loud, I did this all day long in Excel 2003, with the exact same files (only they were even bigger then, saving them as xlsm and xlsx reduced the size to a third), and on an old XP machine: Intel Core2 duo E8500 3.13 GHz, 6MB Cache, 4BG RAM, and on a 512 MB nVidia Geforce 8800GT.
NOW, I'm running on a brand new machine: 6th Gen. Intel Core i7 4.2 GHz, 8 MB Cache, 24GB RAM, on a nVidia GeForce GTX 750Ti 2G DDR5.

So, what's the problem?? Microsoft surely don't know. Their 'solution' is to re-install Office 2016. Well, I've done that 3 times now, no change.

 

[mole999]

the joys of computer support, READ THE SCRIPT

Win 10 is fine, had issues with the thing going to sleep and not being able to wake it, so ended up turning that off, real begger after software updates over night

at home I usually use 2007, but have 2003, 2010, 2016 (did have 2013 installed but it was removed without my agreement)

I don't actually spend time in 2016 if I can help it and it stole all my file associations to become the default and a struggle to get them pointed back to 2007

My machine is i7 with 64 bit OS, though having run 64bit excel, I junked that and went back to 32bit to keep compatibility with add ons

I hated the 2016 animation and slowness so ended up turning OFF the acceleration that was offered

my machine is set to always ON in power settings and I generally don't allow anything to sleep or go into low power mode plugged in (except harddrives at 20 mins), i also turn off bells and whistles trying to get functional windows only

as for reinstalling, it really is the first option to start afresh, like making sure the machine is plugged in. I have something like 120 processes running, high band internet, on a home hub with lots of wifi about so its never quiet

 

[Kenneth Hobson]

Good deal.

At some point, I will make it work in 2.2.2. The 2.1 seemed to allow some of the 1.7 routines. The examples are in jscript and not many of those. IF you open the CHM file, you can more easily search for documentation than the web site. Search for Migration to see what was changed.

Change is not easy and even less so since we have to make it work in VBA rather than jscript.