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