Extra blank page while Quick Printing, only on some PCs

[bansgoo]

Hello everybody!

I work in IT service of a plant. We have some PCs with MS Office Professional Plus 2010.

Our lab creates Excel files (quality certificates) made from the same one template (we can't make changes to the template). Example of a file attached. These files consist of only one sheet with information on it that entirely fits to one printed A4 page.

These files are printed very often in different departments. So I tested one of these files' printing from different PCs. I noticed that on some PCs Quick printing of file gets it printed in one page as it's supposed to. But on some other PCs quick printing gives two A4 pages - one as usual and other is just blank. As there are many files printed, this leads to huge paper overspending.

It's THE SAME file so it was surprising for me. I checked "Page setup" menu (at the bottom of "Print" menu), checked printing preferences - everything on those Pcs is identical. I found out that if I decrease the bottom margin in Page Setup on "problem" PC, file prints on one page, BUT changes are not saved so it's not the solution (we need to print file right from Windows explorer, not opening Excel App explicitly).

Naturally, different PCs use different printers. But I suppose it can not be related to a problem.

Can you give me a clue how to make that file fit to one page on all PCs via quick printing?

[Greg M]

Hi there,

Try inserting the following code into the ThisWorkbook VBA CodeModule (not into a standard CodeModule) of your template workbook, and see if it does what you need:

Please Login or Register  to view this content.

The code assumes that the template worksheet will always be the first worksheet in the workbook.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[bansgoo]

Hi Greg,
Actually just today I found out that there is not one template, but about several hundreds of them - each one for the one customer.
So I can make such a modification of all those files (simply set printing area or just cut off some merged cells at the bottom) but it would take some time. Probably more than this isue deserves
You see, the thing here is that on some PCs it works fine, and on some it doesn't. I suppose there could be some option in Excel app that can be easily set once and forever)

[Zer0Cool]

There could be multiple factors. As you replied, each file could have its own settings causing the difference.

Its also possible that different printers have different feasible margins, and on some the margin has to be slightly bigger and thus some invisible content gets pushed down to a second page.

Its possible its a print driver issue. A good way to test this would be to "Print" to something like XPS or PDF and see if the result is the same 2 paged print out or if its corrected when printing to something generic like a software printer. If the PDF/XPS prints fine its the printer/driver, if not its likely something in the file and/or print settings either within Excel or the OS.

You could check the print settings, specifically the fit to settings. The code above essentially sets this, but you could check manually too. You can set in page setup to either scale by a % or to fit to x pages tall and x wide. If this isnt set to x tall by x wide, you can set it to 1 wide by 1 tall forcing it to fit the entire sheets print range on 1 physical page. This could resolve the issue but is also file specific. If you find in doing this that the 1 page has alot of empty space on it and content is too small, then its likely there are cells on that particular sheet/file that have excess content. You would have to eliminate/clear the excess to fix the problem.

Hope that helps.

[Greg M]

Hi again,

Thanks for your feedback.

If all of the template workbooks are stored in one folder (or in just a few folders) it should be possible to write a routine which automatically opens each template workbook in turn, sets the appropriate PageSetup values, saves the modified workbook, and then closes it.

Does this approach sound as if it might be suitable?

Regards,

Greg M

[Zer0Cool]

Hi again,

Thanks for your feedback.

If all of the template workbooks are stored in one folder (or in just a few folders) it should be possible to write a routine which automatically opens each template workbook in turn, sets the appropriate PageSetup values, saves the modified workbook, and then closes it.

Does this approach sound as if it might be suitable?

Regards,

Greg M

That is certainly possible. I would recommend making copies of about 10-20 and placing them in a folder to test this and see if it even resolves the issue. Stepping back manually changing one misbehaving file and checking its corrected may be prudent.

If it does help resolve things then a VBA routine (or any programming language of your choice) could do this.

In VBA I would tend to use Dir to loop the files in a folder. You can either direct in the loop or in another sub the loop calls open the workbook, change the desired settings, save and close.

You would likely want to do this when you are sure no 1 has any of the files open/in use.

The concept would be generally the same in other programming languages/scripts.

[bansgoo]

Thank you very much guys!
I think I've got enough information for now. I'll try it soon and post a solution I'll choose.