Thanks Nick,
Unfortunately your code does not find any page breaks. I tried creating a
range corresponding to the whole pages rather than just col A, but still no
page breaks.
There is a PrintArea set for the sheet with page breaks between groups of
related rows. I am working with workbooks I did not create so I don't know
how it was set up, but I could see the blue lines defining each page.
I had previously tried the sample below from the Excel help but it shows
only two horizontal breaks in a range where there are eight manual breaks, I
would assume they were partial.
For Each pb in Worksheets(1).HPageBreaks
If pb.Extent = xlPageBreakFull Then
cFull = cFull + 1
Else
cPartial = cPartial + 1
End If
Next
MsgBox cFull & " full-screen page breaks, " & cPartial & _
" print-area page breaks"
I had read something about needing to set ActiveWindow.View =
xlPageBreakPreview in order to get correct numbers but I run my application
(VB6) withouth making Excel visible, so I tried that and now I get two full
and six partials.
The problem now is that I have not figured out how to find the rows for the
page breaks.
I had originally thought that what I saw was one PrintArea for each page,
but I ran some debug code to show the properties of the print area(s) but I
got just one PrintArea covering the whole sheet, so I concluded that what I
saw was one PrintArea broken up by manual horizontal page breaks.
I have written a VB6 application to parse large Excel files and break it up
into many smaller files.
The application breaks up the large file into small files by stepping
through all the rows in each worksheet monitoring data in col A (Hidden).
When the data changes that signals the end of a group of rows, so it selects
the rows with the same data then ues PasteSpecial to paste them into the
last row of copy of a 'template' sheet created from the current sheet by
deleting all rows except the first 5 rows. (This is because the first 4 rows
contains sheet titles and column header names. This way the 'template' file
contains the header rows and one data row, and the PrintArea encloses the 5
rows.)
After pasting the rows the bottom of the PrintArea is dragged down to
enclose the last row using the FixLastRow method and the sheet is saved as a
workbook with a name based on what was in col A. The application works as
originally intended.
In all the sample files my client had provided each new sheet contains only
one page so there were no page breaks within the PrintArea.
However, my client just told me that for some files there are sheets where
they need to break up the contents of a small file into several pages.
Unfortunately, when the group of row are selected and pasted, the page
breaks are not included since they do not come across to the samll file. So
I am trying to detect where the page breaks in the original file are located
and set them accordingly in the small file.
I was hoping that maybe I could manage to get the page breaks included when
I paste, but maybe that is impossible, they might be stored as a row number,
which would need to change. In any case, I have not found anything on this
anywhere.
What I find confusing is that PageBreak is a property of a Range object,
while HPagebreaks is a collection of horizontal page breaks and it is a part
of the Charts, Sheets and Worksheets collections and of the Worksheet
object.
Will the real pagebreak please stand up!
Any help would be appreciated.
Ragnar
