Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Printing various pages from Excel (2002)

    (I don't know VBA) I posted this question in the Excel group and didn't get a solution. A friend of mine (traveling for two weeks, hence this post) wrote a solution for me as an Excel Add-In. The issue is to be able to print different pages from different sheets in an Excel workbook so that the user could print pages 1, 3 and 7 from Sheet1, 2 and 4 from Sheet2, etc. Since the resulting file will be printed to a fax printer, the solution was to create a new file with each tab being one of the pages to be printed. The add-in almost works perfectly. There seems to be on some of the new pages (new file) an extra line not in a page selected; and, the format of the original page(s) isn't maintained. Can't the latter be done in the VBA with various Paste Special instructions?

    Can anyone offer a clean-up and fix for this? Much Thanks!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing various pages from Excel (2002)

    1. Is the extra row (line) always below the last row you wanted to print?

    2. Which aspects of the formatting aren't preserved?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Printing various pages from Excel (2002)

    Thankis for taking a look at this. I've attached a "dumb" file I have used for initial testing. When I try to use the add-in and "print" pages 1 and 3 from sheet1 and page 1 from sheet2, the two "first" pages have an add'l line at the bottom (from the next page).

    I have a much more complicated and real Excel file that bombs totally when the add-in is run. I get a run time error "9" subscript out of range and the de###### shows this as the problem line: For Each objHbreak In objSheet.HPageBreaks

    Thanks for looking at this.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Printing various pages from Excel (2002)

    Forgot...re formatting.
    Would like to preserve the column widths, numeric formats, etc. and equivalent to Paste Special Values -- no formulas just values.

    Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing various pages from Excel (2002)

    The page breaks are not handled correctly. The row or column number of a page break is that of the row below / column to the right of the page break. To take this into account, change this line in the PrintPages procedure (in the PrintPages module):

    Set objEndRange = objSheet.Cells(arrRows(i), arrCols(j))

    to

    Set objEndRange = objSheet.Cells(arrRows(i) - 1, arrCols(j) - 1)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing various pages from Excel (2002)

    To preserve some of the formatting, change the following lines in the PrintPages procedure:

    Dim i As Long, j As Long

    to

    Dim i As Long, j As Long, k As Long

    and

    objLastCell.PasteSpecial

    to

    objLastCell.PasteSpecial xlPasteValues
    objLastCell.PasteSpecial xlPasteFormats
    For k = 1 To objPage.Range.Columns.Count
    objSheet.Columns(k).ColumnWidth = objPage.Range.Columns(k).ColumnWidth
    Next k
    For k = 1 To objPage.Range.Rows.Count
    objSheet.Rows(k).RowHeight = objPage.Range.Rows(k).RowHeight
    Next k

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Printing various pages from Excel (2002)

    As usual, Hans, you are a life-saver. Thanks for those two fixes. They seem to work just fine.
    Now, to the REAL file...I can't attached even the zipped version because it's 760K zipped. But, the add-in is bombing before I get the menu.
    I'm going to past the zipped file on a web site. Maybe you'll be kind enough to give this one a gander also.
    www.modelfitness.com/materials.zip

    Thanks again!!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing various pages from Excel (2002)

    If there are non-contiguous areas, some of which are offscreen, not all page breaks will be initialized. The following addition (2 bold lines) will avoid the error, but also reveal a new problem:

    ' Find the last cell on the workbook
    If objSheet.PageSetup.PrintArea <> "" Then
    Set objLastCell = objSheet.Range(objSheet.PageSetup.PrintArea).Speci alCells(xlCellTypeLastCell)
    Else
    Set objLastCell = objSheet.Cells.SpecialCells(xlCellTypeLastCell)
    End If
    objSheet.Activate
    objLastCell.Select

    It turns out that the code does not determine the last cell correctly. If a print area has been set, objSheet.Range(objSheet.PageSetup.PrintArea).Speci alCells(xlCellTypeLastCell) still finds the last cell of the workbook, not of the print area. I don't have time now to investigate this.

Posting Permissions

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