PDA

View Full Version : Solved: Printing to a Single Job with Dynamic Print Ranges



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?

daniel_9
12-22-2012, 04:58 PM
Update: Maybe what I'm looking for is a way to print individual pages to a single PDF through adobe acrobat. That would be fine, but I'm having a hard time tracking down resources on how to create prints in Acrobat through VBA. Any resources would be welcomed.

Trebor76
12-22-2012, 07:37 PM
Hi Daniel,

Welcome to the forum!!

I see from the extension of the workbook that you've attached that you're using Excel 2007 or later. These versions of excel actually have the ability to write a PDF via clicking the Office button (top left-hand corner of any workbook) and then selecting the PDF or XPS option from the Save As menu item. You can simply record the steps to create the PDF's.

HTH

Robert

daniel_9
12-23-2012, 10:21 AM
Thank you for your reply.

This method gives me code to save PDFs based on the file, true. But then I would have to manually concatenate all the files anyway. I don't see this method as giving me the fine-grained control over printing that I'm looking for.

Rather, since I'm using Acrobat I will probably need to review the inter-application communication APIs for Acrobat. That gives the ability to start a PDF document and then add pages to it as you go before closing/saving the document.

But it's quite complicated and a bit beyond my current knowledge. If anyone has any pointers on how to implement this kind of thing, I'm all ears. Again, though, if you have a better idea that's simpler, that might be better.

Aussiebear
12-23-2012, 04:16 PM
Have a look here at this link and in particular within the Resources subsection
http://www.khk.net/wordpress/2009/03/04/adobe-acrobat-and-vba-an-introduction/

Trebor76
12-23-2012, 04:25 PM
I may have missed the point, but in your original post you say all the print jobs are in the same workbook just in different sheets. If you select all the relevant tabs and then save as PDF or XPS (as above) it will create one PDF with each page for each tab in the workbook.

mdmackillop
12-24-2012, 10:20 AM
A workaround

Copy and paste picture of your areas each to a separate sheet, fit to one page, print as an array

Sub PrintAreas()

Dim LC As String
Dim sh As Worksheet
Dim ws As Worksheet

Set sh = ActiveSheet
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))

sh.Range("area1").Copy
ws.Pictures.Paste
LC = ws.Shapes(1).BottomRightCell.Address(1, 1)

ws.PageSetup.PrintArea = "$A$1:" & LC

Macro5 ws

Set ws = Sheets.Add(after:=Sheets(Sheets.Count))

sh.Range("area2").Copy
ws.Pictures.Paste
LC = ws.Shapes(1).BottomRightCell.Address(1, 1)

ws.PageSetup.PrintArea = "$A$1:" & LC

Macro5 ws

Set ws = Sheets.Add(after:=Sheets(Sheets.Count))

sh.Range("area3").Copy
ws.Pictures.Paste
LC = ws.Shapes(1).BottomRightCell.Address(1, 1)

ws.PageSetup.PrintArea = "$A$1:" & LC

Macro5 ws

End Sub

Sub Macro5(ws)
With ws.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.PrintCommunication = True
End Sub

daniel_9
12-25-2012, 12:21 AM
Ooh - I really like this idea. Thank you! I can definitely get this to work. I could modify your macro slightly so that in addition it will pull headers and footers and delete the pasted sheets after use. This is a great workaround. Thanks! This saves me a huge amount of effort with Adobe Acrobat interapplication communication code!