I’m not hopeful of a solution to this as one has a workaround. My client (and accountant) has a pretty complex Excel document with accounts. Their requirement was to write certain sheets from the workbook to a password protected single PDF. PDFCreator is ideally suited to do this and it wasn’t too much work to work up the solution in the lab. Cutting out all of the surrounding code, it comes down to two lines:
Worksheets(Sheets).Select
ActiveWindow.SelectedSheets.PrintOut
Sheets is an array of the sheets to print. This worked perfectly in my lab but when I glued it into their real Excel workbook, we hit a problem. In my example workbook, all the sheets were A4 portrait. In the real workbook, one of the sheets in the middle was landscape - so it’s trying to print portrait, portrait, landscape, portrait, portrait. But what we got out was just the first two pages.
This is because my code, at present, just processes the first print job. I wasn’t expecting multiple print jobs. And indeed, if you look at PDFCreatorQueue.Jobs.Count, we’re getting a count of three back.
But when I go back to my test bed and set-up the same layout, I get one print job. Also, if you select the sheets manually in the live version in Excel and print to PDF directly (i.e. ignore my code), you get three print jobs which one can merge manually.
I’ve done a quick new replacement sheet for this one sheet and that works fine. So there is something
on this one sheet that’s causing this multiple print job scenario.
So I guess the question is what is causing PDFCreator to stop writing pages to the first job, create a second job for this landscape page and then a third for the remaining two portrait pages?
My guess was some layout difference, e.g. margins, paper size, etc. but I’ve compared my test bed with the live version until I’m blue in the face - nothing leaps out as unusual about this one page.