daniel_9
12-22-2012, 04:39 PM
New here - just joined because this place looks like a great resource. I hope I can be of help to other people as well after this!
So the question I'm asking is sort of a two-part question. I have a problem that I solved one way, and my solution contains a problem that I'm trying to fix (the topic of this thread). If you have a better solution to the original problem, though, I'm all ears about other ways around it.
So the ORIGINAL problem was this: I have sheets with multiple print areas that I want to print. Each print area is an "exhibit" unto itself, so there are no multi-page print ranges. When I print one of these sheets (that contains multiple "exhibits"), I like to have the sheet configured to auto-fit the print size using the "fit to __ page(s) wide by ___ page(s) tall" setting in the print settings dialog. Because each exhibit is an individual page, I set it to 1 wide x 1 tall. Follow me so far?
Here's the first problem I encountered: If one of the exhibits on a page is too big, the "fit to __..." setting will shrink it considerably to fit on a page. That's GOOD - I want it to fit on a page. The problem is that having this setting enabled also shrinks ALL OTHER print ranges to that size. That is, having the "fit to..." setting enabled will shrink ALL print ranges so that all of them fit in at most the number of pages tall/wide that you specify. So if there's a really huge exhibit that needs a lot of shrinking and a really small exhibit that requires no shrinking, if they are on the same page then they will BOTH get shrunk down.
EDIT: I have added an attachment that demonstrates this. If you check the print preview for the first sheet, the text for Exhibit 1 is tiny because there's a second, huge exhibit that is causing it to shrink down. The second sheet has much larger text for Exhibit 1.
I wanted all the exhibits stretched to fit the screen. So I came up with a workaround: I would print all of the print ranges individually. I wrote a macro that breaks down a worksheet's print range into its constituent sub-ranges, then sets the print range to the first such sub-range, prints, goes to the second, prints, etc. When you do this, the result is that each page is fitted individually, so the resulting set of printed pages has every exhibit filling up the space nicely.
Here's where the secondary problem comes in. This is OK when I'm printing to my actual printer, but if I want to print to a PDF, it's really obnoxious, because my PDF printer (Adobe Acrobat) asks me where to save EACH page printed. Some of my documents have hundreds of exhibits. Doesn't work well.
What I'm hoping to do is figure out a way to "pause" the print job from spooling, then add additional pages to it. So when I run through each exhibit on a sheet, I can print the first exhibit, then add the second exhibit to the print job, and so forth. Then I will run the "print" command for all the exhibits at once.
I've seen a macro to do this for pdfcreator (where it will add subsequent printings to a single print job), but I don't want to download that because this macro needs to work for all our other work computers, and I have no chance of convincing people that we need to install another pdf program to satisfy this relatively minimal problem.
The other possible way around my problem I could think of is something that fixes the way excel dynamically sizes the exhibits, so they're correctly sized (to a single page) when I print. I'm not sure how to do that, though.
Any ideas for either problem would be most appreciated!
TL;DR: How can I get multiple print ranges to be added individually to a single print job?
So the question I'm asking is sort of a two-part question. I have a problem that I solved one way, and my solution contains a problem that I'm trying to fix (the topic of this thread). If you have a better solution to the original problem, though, I'm all ears about other ways around it.
So the ORIGINAL problem was this: I have sheets with multiple print areas that I want to print. Each print area is an "exhibit" unto itself, so there are no multi-page print ranges. When I print one of these sheets (that contains multiple "exhibits"), I like to have the sheet configured to auto-fit the print size using the "fit to __ page(s) wide by ___ page(s) tall" setting in the print settings dialog. Because each exhibit is an individual page, I set it to 1 wide x 1 tall. Follow me so far?
Here's the first problem I encountered: If one of the exhibits on a page is too big, the "fit to __..." setting will shrink it considerably to fit on a page. That's GOOD - I want it to fit on a page. The problem is that having this setting enabled also shrinks ALL OTHER print ranges to that size. That is, having the "fit to..." setting enabled will shrink ALL print ranges so that all of them fit in at most the number of pages tall/wide that you specify. So if there's a really huge exhibit that needs a lot of shrinking and a really small exhibit that requires no shrinking, if they are on the same page then they will BOTH get shrunk down.
EDIT: I have added an attachment that demonstrates this. If you check the print preview for the first sheet, the text for Exhibit 1 is tiny because there's a second, huge exhibit that is causing it to shrink down. The second sheet has much larger text for Exhibit 1.
I wanted all the exhibits stretched to fit the screen. So I came up with a workaround: I would print all of the print ranges individually. I wrote a macro that breaks down a worksheet's print range into its constituent sub-ranges, then sets the print range to the first such sub-range, prints, goes to the second, prints, etc. When you do this, the result is that each page is fitted individually, so the resulting set of printed pages has every exhibit filling up the space nicely.
Here's where the secondary problem comes in. This is OK when I'm printing to my actual printer, but if I want to print to a PDF, it's really obnoxious, because my PDF printer (Adobe Acrobat) asks me where to save EACH page printed. Some of my documents have hundreds of exhibits. Doesn't work well.
What I'm hoping to do is figure out a way to "pause" the print job from spooling, then add additional pages to it. So when I run through each exhibit on a sheet, I can print the first exhibit, then add the second exhibit to the print job, and so forth. Then I will run the "print" command for all the exhibits at once.
I've seen a macro to do this for pdfcreator (where it will add subsequent printings to a single print job), but I don't want to download that because this macro needs to work for all our other work computers, and I have no chance of convincing people that we need to install another pdf program to satisfy this relatively minimal problem.
The other possible way around my problem I could think of is something that fixes the way excel dynamically sizes the exhibits, so they're correctly sized (to a single page) when I print. I'm not sure how to do that, though.
Any ideas for either problem would be most appreciated!
TL;DR: How can I get multiple print ranges to be added individually to a single print job?