Results 1 to 12 of 12
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    print non-contiguous tables (Excel 2k/XP)

    I'd like advice on how to print sub-sections of multiple worksheets (e.g., some rows from Sheet1, some other rows from Sheet3, two charts from Sheet4, etc.). I'll add a print button that has code to print a sub-set of data (in tables that span about 22 columns) from certain worksheets in the workbook. I'll also need to include some charts (that appear on different sheets).

    1. How do I handle printing a wide table if it's too big for landscape? I'd like to 'intelligently' move the page break to a location on the table that doesn't chop off the data too badly (at a location of my choosing).
    2. How can I print one file with all these items (since they're not all on the same sheet) w/o doing individual printouts to multiple files.
    3. How do I fetch the list of available printers for the current user?

    Solutions I've dreamed up (not tested yet):
    1. Create a named range that includes these data tables and somehow include the charts too. Does this require multiple print commands (I want one document to print not one per data range)?
    2. Create a view that only shows the desired tables and charts and print that view (with the print setup required). Can I print the view w/o displaying it (I don't want the user to see this view flicker on the screen and then have to restore the normal view). <img src=/S/groan.gif border=0 alt=groan width=16 height=15>
    3. Copy all the tables and charts to a separate hidden worksheet and just print that one worksheet. This sounds great but I need to make sure that these copies get updated if their 'twin' gets changed. Plus all formatting needs to be retained as well, not just the cell data. I tried the camera tool but these tables are too wide for that to work.

    Attached is a sample workbook plus code that launches the print form with dummy printers.

    Thank you all oh-wise-woody-ites <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Deb

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    Deb,

    One possible way to handle different print requests would be through a form. By clicking one or the other checkboxes, you can set the print options based on which checkbox is selected.

    As far as selecting the available printers try: Application.Dialogs(xlDialogPrinterSetup).Show

    John

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    Actually that's what I'm trying to do except that the user doesn't have a choice of what gets printed. Nothing is available for printing other than the sub-set of items in the workbook that I mentioned.

    Let me clarify your mention of Application.Dialogs(xlDialogPrinterSetup).Show... This brings up the dialog box, of all available printers, great! So would I display this after I've accumulated all the items to be printed such that they end up in one document? The user then selects a printer of their choice and presses OK. When I run this one line of code to display the printer list, pressing OK does nothing. I'm obviously missing something trivial here <img src=/S/doh.gif border=0 alt=doh width=15 height=15> How do I queue up my data such that they get printed when the user selects a printer from this list?

    Deb

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    Deb,

    Click here. I was having the same fun some time ago.

    John

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    Ok... the fog is lifting a little bit... I read the post you mentioned and others that it referenced as well.

    1. Display 'xlDialogPrinterSetup'
    2. If return True (user hit OK button) then accumulate items to be printed (still unknown how to do that such that ONE page is printed not one per object)

    Or do I accumulate the data first into some print queue and then on OK from the printer dialog, it magically gets written to the chosen printer? I'm confused since displaying the printer dialog does nothing when I press OK (other than return Trues/False). It seems that this just sets the activeprinter and I then have to use .PrintOut to define what gets printed. Right?

    I see the .PrintOut method applies to many objects including Ranges and Charts so that's how I assume I'll get the items to the printer (but it still seems like it'll be multiple files not just one. For example, one table is only 10 rows so I don't want it on its own sheet, I'd also like to have other things that fill up the printed page.

    This would be a no-brainer if all the things to be printed were on the same sheet in the order required. I think I need to figure out how to get a copy of these data tables and charts onto a hidden sheet and just print that one sheet. I need to make sure these copies get automatically updated if their master (the original) gets changed.

    Deb

  6. #6
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    Deb
    I'll leave the printing dialog to others.

    I think your summary is correct - have a hidden sheet (formatted as you want) which is your printout

    So long as the ranges from elsewhere are static it should be easy enough to achieve automatic updating as in

    A1 on your print sheet has the formula =OtherSheet!ref for every cell that needs a value

    If not - I'm think a set of named ranges (e.g. PrintArea1 through PrintArean and a Macro to work to copy them - could simply be created in the Macro recorder, or generalised
    via a loop.

    HTH

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    On the hidden sheet of "combined ranges" you could add "pictures" of the ranges you want to print arranged how you want them.

    You can use the camera tool to take "live pictures" of the ranges and add them to the hidden sheet. The advantage of the camera tool is that it is a live picture: when the contents or formatting of the range is changed it is reflected in the output

    You can also resize the picture so that all of the ranges do not have to be at the same scale

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    In addition to Steve's excellent suggestion about the camera tool, you could use dynamic range names that define the area the picture is to show. Simply select a picture, click in the formula bar and enter the dynamic name's name preceded by the equal sign. You can use the same trick to change the range a picture is taken from: click the formula bar, change the shown address.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    THANKS for all the great replies and suggestions <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> I now have a plan and have learned a few new tricks (always a good thing).

    I just got a copy of the actual workbook that my co-worker wanted me to write code for printing. After seeing it, I realize that it wasn't what I thought it was. <img src=/S/groan.gif border=0 alt=groan width=16 height=15> For legal reasons, we can't provide the customer with the actual workbook so she's done the data entry for them and wants to provide them with the results only (the model output, the answers). It also turns out that she wants almost every table and chart printed as well (not a small sub-set as I had assumed). <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    So knowing this, I suggested to her that I create a data-only copy of this entire workbook with charts saved as pictures. That way the formulas are gone and the customer can't use the model to create a different output. I also don't have to deal with formatting the tables such that they appear nice in a PDF file (with proper spacing, cell labels and column headings repeated as needed).

    Do you think that's the best solution given that I now have all the facts? <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

    Thnx, Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    I haven't created pdf files, but that seems to be the preferred way to give preformatted reports with some protection from editing.

    Steve

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    After several hours of writing code to dynamically create the new workbook with copies of the data and charts, I've decided to just use the camera tool trick afterall. This task I'm doing is just a one off thing so I can't see the point in spending a long time writing cool, generic code. So I duplicated each page to be copied (renamed them with prefix "ro." for read-only) and made copies (via the shift/edit key combo in the suggested link) to these new sheets. I'll then write code to copy all the "ro." sheets to a new workbook. Then this new workbook can be sent to the customer w/o concern that they can edit/change anything. Much easier task than what I was going to do (dynamically select/paste ranges). <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    The trick mentioned on John Walkenbach's site is great!! I didn't know about the shift key when selecting the Edit menu. One thing not mentioned in his article that I wanted to pass on.... <img src=/S/compute.gif border=0 alt=compute width=40 height=20> This doesn't work as stated for charts! <img src=/S/drop.gif border=0 alt=drop width=23 height=23> To get a 'live' copy of a chart, you can't just selected the chart and copy it (like you can tabular data). You have to select the area behind the chart as if you were drawing a frame around it (select the upper left cell behind the chart and move to bottom right of chart area). This will result in pasting a border with the chart but it does keep the data dynamic.

    SO COOL!!! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    Deb

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: print non-contiguous tables (Excel 2k/XP)

    I ran into a problem!!! <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> <img src=/S/bash.gif border=0 alt=bash width=35 height=39> When I gave the Excel files to the person who asked me to do this project, she kept getting errors when she opened it. Excel reported "..the picture is too large and will be truncated." She had to kill Excel manually to get out of this error (pressing OK just kept repeating the error message). I do not see that error on my computer and we both have Win2k with the same service packs. She has 512KB and I have 768K RAM on our laptops.

    If I manually shrink the copied pictures (after I pasted them), will that get rid of this error? It's hard to diagose when I don't see the error.

    EDITED. I found the error in MSKB, http://support.microsoft.com/default.aspx?...b;EN-US;Q318265
    I'll ask my co-worker to download the hot fix and see if it works. MSKB shows it as Excel 2000 but she has Excel 2002.

    Deb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •