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]

Don't even get me started on so called tech support.

No problems, I only wish. I don't use Excel 2016 much since it is only on my personal laptop. When upgrading Windows versions, I find that it works best to get it with a whole new system. I have never upgraded old hardware to new Windows versions without problems. I had hoped that Win10 would be different. In the first install, it lost my wifi which I must have. It then made charging max at 87% and still show charging. When battery gets low which is fairly fast, it just closes without notice losing all work in progress. In the latest update patch, it again lost wifi on this Asus laptop. I then restored to earlier version and then the update worked allowed wifi to start again. Vent mode off..

Working with big Excel files has always been a problem for me. I do like to have 4 or so open at once and many others as well. At one point, we were working on 20 mb files. I had to split it up to even allow the files to emailed and even then it overloaded our email allowances. I started sharing files through an FTP site that we set up. I am working on getting a mysql server setup to skip Excel alltogether using vb.net with mysql.

We tend to stay 2-3 versions back at work. We just went to win7 6 months back and have been using Excel 2010 for some time with good success. I guess slow to upgrade is not a bad Enterprise scheme.

 

[JytteC]

I am also running 2016 32-bits.

Now, mole, you said you have 2003 as well. That runs on windows 10?? Isn't there a file format problem then?

I have been sorely tempted to junk 2016, but bought keys for it via Amazon, and I'm not sure if I can get my money back ( times 3).
To quote Microsoft Tech: About your questions, if you bought office 2016 directly from microsoft store, if it is still with in 30 days, you can contact our MS store costumer service hotline to have your product replaced or refund. If it was bought from outside Microsoft, you can try to contact Microsoft Money-back-guarantee, provided that 1. you have the receipt and key card of the purhcase 2. it is still with in 30 days. Just to set your expectation, It is with in that department's policy if your case is eligible for the refund. *crossing my fingers. About your questions, if you bought office 2016 directly from microsoft store, if it is still with in 30 days, you can contact our MS store costumer service hotline to have your product replaced or refund. If it was bought from outside Microsoft, you can try to contact Microsoft Money-back-guarantee, provided that 1. you have the receipt and key card of the purhcase 2. it is still with in 30 days. Just to set your expectation, It is with in that department's policy if your case is eligible for the refund. *crossing my fingers.

I also wouldn't know WHICH version to get instead, and loathe the thought of having to do 3 new all over installs.

When I ran on 2003 (on XP), it was getting increasing slow and hard running the files, as they kept getting bigger. Like I said as xls the files were way bigger. I figured it was partly my old not-so-big-anymore computer, partly 2003 being 'too small'.
Well, some things actually run SLOWER in 2016

IF I could run 2003 on my PC, wouldn't it NOT be able to use the sizeable RAM I have now, That's what I understood ? Then I'd be back to square one.

Another problem, I have only ONE 2003 (disc), but now need to run it on 3 computers. Where on earth would I get 2003 any more?

Sorry for all the questions, but I'm at the end of my rope.

 

[JytteC]

Yet another question: IF I was to try to install 2003 on my PC, will that interfere with the present 2016 installed?? Can I run both?

 

[JytteC]

Kenneth, slow to upgrade is exactly my motto !! Which is why I hung on to my old XP, and my 2003. Only then the XP was dying on me (and also getting kind of small and slow for all the things one do nowadays), so suddenly I had no options other than to buy a new PC. In doing so I found out that I could ONLY get a new PC with Win 10. So I did, and I have to say, Win 10 isn't that bad all things considered. Better than 8.1 at any rate from my experience.

I don't feel I'm doing anything 'drastic' with my Excel, I mean 2003 handled it just fine until the files got real big. And I was under the impression that 2003 would not run on Win 10, and then I thought, well I'll just get the newest, to make sure I'm ok for a long time to come. I also cannot install and set up 3 computers while working, so timing seemed to be good, as we're closed over Xmas. Got the machines 28th of December, and spent the next 2 weeks getting everything set up and ready to go. 24/7 that was, yea that was my so-called much needed Xmas vacation down the tubes!

So it had to happen fast and it had to happen now. MY BAD. I should have gotten the trial version to keep my options more open. Now I don't really know what to do.

 

[mole999]

2003 boots really quickly, it hasn't undergone the XLM updates and no 32bit is going to address more than 4Gb, 2003 can't exceed 256 columns and 65536 rows. I think 2010 is the last being supported. MS won't sell anything less than 365 or 2016, but you will find retailers with valid stocks.
2016 will be where everyone gets to in the future, I get on well with 2010 trimmed down to my needs, and i use xlsb normally. xls is still valid, but you need to update to work with the 2016 comfortably, and then new features don't depreciate so its quite useful to use the old features so that code is similar.

Generating code for tables and pivots works better in 2003 for me, than just set in the newer environment (not fool proof)

If putting 2003 on cause issues, just detect and repair the latest version the exist together comfortably

 

[JytteC]

Just wonderful. I had just gotten all the pdf stuff fixed. Then I lost my commandprompt (without whitch none of my batch files run obviously)..... this is just the 3rd time in 4 days. Had to do a system restore to get it back, which also menat I lost everything I just did. Had to clean up the mess, then reinsttall pdf TWICE, before I finally got it to save class in regedit. Works again now, and I have created a new restore point, just in case.

problem is I don't know why I get all these problems, and whether it's win 10 or 2016 creating them. All I DO know is that Microsoft sure as H*** does NOT know. A system restore every 2 days to function???????

I don't have a problem with the limits on columns/rows. Maybe I should try to install 2003 and see what happens. It can't get much worse I guess.

Using 2003 would then mean, I'd have to go back and use the old files again. hmmmmm, I wonder....

 

[mole999]

MS won't support 2003 but if that works 100% for you, for the meantime worth doing. I don't like 10, but I can tolerate it if used as just software rather than all the integration etc. I think a number of bug fixes have left the software not as stable as it will become. If hubby isn't having your problems can you swap machine sfor a few days and see if it only crashes whilst you are using it ?

you can use taskmanager to restart a stalled system, i have to on occasions for explorer, you must be able to restart cmd from there also I guess

 

[JytteC]

Well, I've had it up to HERE with 2016!

I just re-installed 2003 and it works just fine. The only thing is every time I open the program anew, it keeps giving the 'accept this license', although it did go out and activate the software. I think this probably has something to do with the fact that MS don't support it anymore. It's a bit annoying, but since I rarely open the program as such more than once a day (open some files in the morning, then open/close all manner of other files during the day, but base stays open), it's a small enough price to pay.

When I set up this system some years back, I was smart enough to make little routine that saves in a values-only file the transactions each and every day. That came mighty handy now, as I was able to pick up my old xls files from 2 weeks ago, use the backed up files to recoup all transactions since then. And I'm back to my old xls files updated to today.

I've been spending the last hours removing all the 2016 files that I so painstakingly made, and having to redo all my shortcuts and batches back to where they were before. Weeks of work down the tubes.
This also means I'm back to very big files unfortunately. I'm wondering if there's something I can do to streamline them a bit. But not tonight anymore. I still now have to fix 2 more machines, and all before Monday morning.

BUT, I have my good old trusty Excel 2003 back on my machine. It works great, is actually quite a bit faster now, well on the new machine it should be. So I'm HAPPY!
And I'm back to having my files open instantly, instead of staring at a blank screen for a while, and no more sluggish movements in the sheets.
I could give a hoot if MS supports it or not, haven't needed them in the last 12 years. I'll just say a prayer that 2003 will last me another decent span of years, and by then I'm retired and couldn't care less LOL

I will now remove EVERY LAST trace of 2016, and forget I ever even HEARD of this crap.

restarting cmd from task manager? well, I'll have to try that if or when it fails again. Thanks for the tip.

 

[mole999]

I would say keep 2016 (or get a 2010 office install) installed, if someone sends you a file in the later format you won't be able to work with it, you can't test your 2003 versions for up-scalability should you need to provide a file somewhere. Turn off hardware acceleration, install features that you won't need to use. It will be there when you need it, but you can carry on working as you were.

Have a read here > https://support.microsoft.com/en-us/kb/884202

 

[JytteC]

I have already packed up the keys, they go back to Amazon. Deleted all traces of 2016. And don't ever want to see it again.
I don't know if I want to consider another newer version than 2003, if I do I'll take my sweet time and get a trial if possible. This was just way too much hassle.

Fortunately, I only ever exchange files with a business partner (on another continent), who gets daily updates. Anything and everything that goes 'outside' our little world is pdf. He and I always run the exact same system, so no problems there.

From what I've experience the last couple of weeks, I sincerely doubt I'd ever want to get anything other than 2003. It's fast and does everything I need.

Only thing is my files are getting real big. I will have to figure out a way to trim them down, or I will get problems down the road.

Thanks for the link, I will try to get rid of my nag screen