Hello,

How would I go about finding locations of horizontal pagebreaks by stepping
through all the rows in a sheet?

I am already stepping through the sheet looking for presence of a certain
value in a column and I would like to locate any manual pagebreaks as I go.

I have looked at all the documentation in Excel but I find it kind of
convoluted and confusing.

Any help would be appreciated.

Ragnar

Ragnar

Something like this will do it

Sub DiscoverPageBreaks()
Dim rng As Range
Dim pbState As Integer
Dim lManBreak As Integer, lAutoBreak As Long
lManBreak = 0
lAutoBreak = 0
For Each rng In Range("A1:A500")
pbState = Rows(rng.Row).PageBreak
If pbState = xlPageBreakManual Then
MsgBox "There is a manual page break at: " & rng.Address, vbOKOnly
lManBreak = lManBreak + 1
ElseIf pbState = xlPageBreakAutomatic Then
MsgBox "There is a automatic page break at: " & rng.Address, vbOKOnly
lAutoBreak = lAutoBreak + 1
End If
Next rng
MsgBox "There are a total of " & lManBreak + lAutoBreak & " page break(s)" &
Chr(13) _
& lManBreak & " manual page break(s) and " & _
Chr(13) & lAutoBreak & " automatic page break(s)", _
vbInformation + vbOKOnly
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Ragnar Midtskogen" wrote in message
...
Hello,

How would I go about finding locations of horizontal pagebreaks by
stepping through all the rows in a sheet?

I am already stepping through the sheet looking for presence of a certain
value in a column and I would like to locate any manual pagebreaks as I
go.

I have looked at all the documentation in Excel but I find it kind of
convoluted and confusing.

Any help would be appreciated.

Ragnar

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

Ragnar

This was tested and worked in Excel 2003... I'll need to look back if the
property changed, but it should work...what version are you coding for?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Ragnar Midtskogen" wrote in message
...
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

Tom Ogilvy posted this a couple of years ago. It uses the older XLM stuff (it's
quicker).

http://groups.google.com/groups?thre...%40tkmsftngp07

Ragnar Midtskogen wrote:

Hello,

How would I go about finding locations of horizontal pagebreaks by stepping
through all the rows in a sheet?

I am already stepping through the sheet looking for presence of a certain
value in a column and I would like to locate any manual pagebreaks as I go.

I have looked at all the documentation in Excel but I find it kind of
convoluted and confusing.

Any help would be appreciated.

Ragnar

--

Dave Peterson

Thank you Dave,

That looks interesting, I will play with that when I am a little more awake.

Ragnar

Nick,

I have 2000 on my machines, and the client has mostly 2000 now, but I am
sure they will upgrade when new machines are bought.
So, I will need code that does not break when run on a newer version.



I find it hard to believe that Microsoft would come out with versions of
Excel that are not backwards compatible in the sense that older applications
would work with a newer version of the program. If nothing else, they would
provide a means of converting a file to the newer version like they have
done in their other Office programs. In fact, I think I have seen that with
Excel, when I opened an Excel 97 file I was given the option to convert to
2000 format. But it opened OK in 2000.

Ragnar

"Nick Hodge" wrote in message
...
Ragnar

This was tested and worked in Excel 2003... I'll need to look back if the
property changed, but it should work...what version are you coding for?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Ragnar Midtskogen" wrote in message
...
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