How to iterate over all the page breaks in an Excel 2003 worksheet via COM

Question

I've been trying to retrieve the locations of all the page breaks on a given Excel 2003 worksheet over COM. Here's an example of the kind of thing I'm trying to do:

Excel::HPageBreaksPtr pHPageBreaks = pSheet->GetHPageBreaks();
long count = pHPageBreaks->Count;
for (long i=0; i < count; ++i)
{
  Excel::HPageBreakPtr pHPageBreak = pHPageBreaks->GetItem(i+1); 
  Excel::RangePtr pLocation = pHPageBreak->GetLocation();

  printf("Page break at row %d\n", pLocation->Row);

  pLocation.Release();
  pHPageBreak.Release();
}
pHPageBreaks.Release();

I expect this to print out the row numbers of each of the horizontal page breaks in pSheet. The problem I'm having is that although count correctly indicates the number of page breaks in the worksheet, I can only ever seem to retrieve the first one. On the second run through the loop, calling pHPageBreaks->GetItem(i) throws an exception, with error number 0x8002000b, "invalid index".

Attempting to use pHPageBreaks->Get_NewEnum() to get an enumerator to iterate over the collection also fails with the same error, immediately on the call to Get_NewEnum().

I've looked around for a solution, and the closest thing I've found so far is http://support.microsoft.com/kb/210663/en-us. I have tried activating various cells beyond the page breaks, including the cells just beyond the range to be printed, as well as the lower-right cell (IV65536), but it didn't help.

If somebody can tell me how to get Excel to return the locations of all of the page breaks in a sheet, that would be awesome!

Thank you.

@Joel: Yes, I have tried displaying the user interface, and then setting ScreenUpdating to true - it produced the same results. Also, I have since tried combinations of setting pSheet->PrintArea to the entire worksheet and/or calling pSheet->ResetAllPageBreaks() before my call to get the HPageBreaks collection, which didn't help either.

@Joel: I've used pSheet->UsedRange to determine the row to scroll past, and Excel does scroll past all the horizontal breaks, but I'm still having the same issue when I try to access the second one. Unfortunately, switching to Excel 2007 did not help either.

Answer 1

Did you set ScreenUpdating to True, as mentioned in the KB article?

You may want to actually toggle it to True to force a screen repaint. It sounds like the calculation of page breaks is a side-effect of actually rendering the page, rather than something Excel does on demand, so you have to trigger a page rendering on the screen.

Answer 2

Experimenting with Excel 2007 from Visual Basic, I discovered that the page break isn't known unless it has been displayed on the screen at least once.

The best workaround I could find was to page down, from the top of the sheet to the last row containing data. Then you can enumerate all the page breaks.

Here's the VBA code... let me know if you have any problem converting this to COM:

Range("A1").Select
numRows = Range("A1").End(xlDown).Row

While ActiveWindow.ScrollRow < numRows
    ActiveWindow.LargeScroll Down:=1
Wend

For Each x In ActiveSheet.HPageBreaks
    Debug.Print x.Location.Row
Next

This code made one simplifying assumption:

• I used the .End(xlDown) method to figure out how far the data goes... this assumes that you have continuous data from A1 down to the bottom of the sheet. If you don't, you need to use some other method to figure out how far to keep scrolling.

Answer 3

I tried the ScreenUpdating=True and HPageBreaks.Count came up consistently with the correct number of pagebreaks. Many Thanks!