2

Update: added issue on Feedback Hub: https://aka.ms/AA65nqn


When I select sheets (Shift+Click to multiselect) in a macro-free workbook that each may or may not span multiple printed pages, select File > Print (using Microsoft Print to PDF, selecting "Print Active Sheets"), all pages don't get included in the PDF.

This happens when using File > Save As (PDF) as well.

Specifically, the last couple of sheets/pages are omitted from the result, which is a huge issue, since it basically renders the file useless for its purpose (= PDF report generation).

File has mixed orientation (Landscape/Portrait), many of the sheets span multiple printed pages, and the last two sheets are static one-pagers that are - even though I selected them - not included in the PDF.

I can reproduce the issue consistently with this example file. Example PDF output also available (note the two last sheets are missing). The last page looks like this:

Last page of example file which doesn't get included in PDF

Check the last page of the resulting PDF. It's not there!

Environment

  • Excel version 1906 (16.0.11727.20222 32-bit)
  • Windows 10 Pro 64-bit

Things I've checked for/tested:

  • All pages have same print quality (was noted as a potential issue elsewhere)
  • Using regular "Save As" and selecting PDF gives the same (bad) results.
  • CutePDF writer gives the same (bad) results.
  • Printing to a physical printer prints all pages (good!)
  • If I select only the last couple of sheets, they get included in the PDF as they should. As I expand my sheet selection and print to PDF, at some point it starts "losing" pages at the end of the document.

Observations/clues:

  • Total page count varies throughout the PDF document. See page 21 and 22 in the example PDF linked above, it says "Page 21 of 31", then "Page 22 of 30" (similar oddity can be observed with CutePDF)
  • Sheets with pivot tables that span multiple sheets seem to be the primary culprit: selecting these for printing seems to have higher likelihood of messing up the output.

I think this is mainly an issue with the print drivers (Microsoft Print to PDF or CutePDF) since printing to a professional printer works as intended, but I could be wrong.

What could be wrong, and how do you suggest I mitigate this? I need the PDF output; this is the primary purpose of the Excel file.

  • It's hard to help without the proper information. What is on the sheets/pages? What code have you used? Can you upload a copy of the sheet and/or pictures of what the last pages that does not print. Preferably the document but pictures may help too. – Andreas Sep 18 at 11:43
  • The book is macro-free, no code is involved. I just Shift+click to select all printable sheets and go to File > Print. Sheets are all just fairly simple pivot tables that span multiple pages each. Ie. one sheet can result in 2-3 pages in the PDF. Last couple of sheets are static/one-pagers. Updated question. – bernhof Sep 18 at 11:54
  • If we can't reproduce the problem then we can't help you either. – Andreas Sep 18 at 11:56
  • Point taken. It was surprisingly simple to reproduce the issue, see the example files linked in the post. – bernhof Sep 18 at 12:18
  • @Andreas I appreciate the feedback, and I understand the downvote is likely related to lack of information. If you feel that I could still provide more information to clarify, please do say so. – bernhof Sep 19 at 12:01
2
+50

I have used your example file to successfully duplicate the problem and I did find a solution.

The problem with this spreadsheet is that you have mixed orientations : Some sheets have the Landscape orientation while others are in Portrait. While printing, Excel forces you to specify one orientation and this does not work, for two reasons:

  1. Printing in Landscape Orientation will print all the selected sheets, but everything will be in Landscape Orientation, even the sheets marked as Portrait.

  2. Printing in Portrait Orientation will stop upon reaching sheet "Sheet2 (6)" which is too wide to print in Portrait orientation. Reducing the column-widths in "Sheet2 (6)" can let it print in Portrait Orientation, except that some columns will be truncated.

Excel simply does not let you print in mixed orientation, which I believe is an oversight of its developers, not adding an option for this case.

The way to generate a PDF file with variable Orientation is not to use a PDF printer. Use instead the ribbon File > Save As and save the selected sheets in PDF format. This will save the sheets, each with its own correct orientation.

  • I think you've misread the print window. The settings displayed apply only to the page currently in the print preview. It's perfectly possible to print mixed orientation. I also don't believe it has anything to do with Print to PDF vs Save as PDF, but rather a more deep-founded issue with regard to PDF conversion in general. Two important observations here: (1) printing to an actual printer works perfectly fine, and (2) if you move the landscape sheet before Sheet2, you'll see it prints pages in both portait and landscape just fine, but will still lose pages in the end. – bernhof Sep 27 at 19:06
  • If I select all sheets to be printed in portrait, it still loses pages. (note that print layout is set to fit sheets to 1 page in width, so they shouldn't be too wide to print in portrait) – bernhof Sep 27 at 19:18
  • True, which is what I found. The Preview shows a perfect print file, but the PDF print itself is buggy and not perfect. I would guess that the Preview shows the normal printer layout and is not at all using the PDF printing software. You could enter a bug report for this via the Feedback Hub, but SaveAs is the workaround for the moment. I think there are more bugs with setting the Orientation in the Preview, as in my and your findings. – harrymc Sep 27 at 19:31
  • Well, at least it's good to know I'm not the only one seeing this issue - which means a bug report could yield results. Important to note that File > Save As does not correct the problem for me. Are you saying that if you download the example file anew and use File > Save As - you get the last two sheets ("Please print me") at the end in the PDF output?? – bernhof Sep 28 at 18:04
  • Yes, I get all sheets when selecting them and doing SaveAs PDF. See my PDF file here. I'm using Office 2019, Excel version 16.0.11929.20298. – harrymc Sep 28 at 18:53
0

As a pragmatic work-around while you try and sort this out, you can use a PDF-editing file to add the missing pages into your main file from the print out of just those pages.

There are lots of free PDF editors of varying quality around. For what its worth, the one I use is called FlexiPDF and woul do the job.

  • We're a consultancy delivering this file as a sort of productivity tool for a customer, so this type of workaround is definitely not a viable solution. Your answer may however be of help to others reading this question. – bernhof Sep 27 at 14:36
-1

Since file is MACRO free, therefore print to PDF is not possible, even using File, Print built in commands.

So that, I would like to suggest a method solves the issue.

How it works:

  • Form VIEW Tab click Page Break Preview.
  • Insure Page layout is correct, or if required alter it.
  • Save the File.
  • From File Menu click Save as.
  • Pick location, then from Save As Type drop down, select PDF.

You find this:

enter image description here

  • Select Standard Publication from Optimize For Radio Button.
  • You may also check the, Open File After Publishing Check box.
  • Now hit Option Button.

You find this:

enter image description here

  • Select an appropriate options as shown in Screen Shot above, or you may alter it as needed.

  • Now, finish with Ok.

  • Finally from previous Popup dialogue hit, Save button.

You find the PDF file open, then you may proceed in the way you want.

  • 2
    Print to PDF is not dependent on macros! – harrymc Sep 27 at 12:30
  • This does not help me, unfortunately. If you believe it should, I suggest you try your method with the example file that I link to. See if the last sheet of the file is included in the resulting PDF. Whatever method I try (save as PDF, print to PDF, whatever options I select) I can't get the last page(s) out. – bernhof Sep 27 at 14:26
  • 1
    @bernhof,, check the Option Dialogue ,, Active Sheet is selected and while Print Preview you can find the pages to save to PDF ,, can be set Page Range in Option Dialogue,, and let me say what I've suggested in fully functional,, I've tested before I've posted here!! – Rajesh S Sep 27 at 15:10
  • Yes, Print Preview displays things as they should look, but with the example file, the resulting PDF does not look like the print preview! I know your method "works" in the sense that it'll generate a PDF that - theoretically - should contain all sheets. But with the example file the PDF will not look as you'd expect. – bernhof Sep 27 at 18:45
  • @bernhof,, you need to check Excel data in Print Preview to insure Page layout & while SAVE As uisng the OPTION you may set choice to print ALL Pages or selected,, PDF is the out come of the exercise,, and what I've suggested is the only non VBA (macro) method to convert Excel Sheets to PDF. – Rajesh S Sep 28 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.