<p>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:</p>

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

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

  pLocation.Release();
  pHPageBreak.Release();
}
pHPageBreaks.Release();
</code></pre>

<p>I expect this to print out the row numbers of each of the horizontal page breaks in <code>pSheet</code>.  The problem I'm having is that although <code>count</code> 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 <code>pHPageBreaks-&gt;GetItem(i)</code> throws an exception, with error number 0x8002000b, "invalid index".</p>

<p>Attempting to use <code>pHPageBreaks-&gt;Get_NewEnum()</code> to get an enumerator to iterate over the collection also fails with the same error, immediately on the call to <code>Get_NewEnum()</code>.</p>

<p>I've looked around for a solution, and the closest thing I've found so far is  <a href="http://support.microsoft.com/kb/210663/en-us" rel="nofollow noreferrer">http://support.microsoft.com/kb/210663/en-us</a>.  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.</p>

<p>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!</p>

<p>Thank you.</p>

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

<p>@Joel: I've used <code>pSheet-&gt;UsedRange</code> 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.</p>

[{'answer_id': 78519, 'author': 'Joel Spolsky', 'author_id': 4, 'author_profile': 'https://Stackoverflow.com/users/4', 'pm_score': 0, 'selected': False, 'text': '<p>Did you set ScreenUpdating to True, as mentioned in the KB article?</p>\n\n<p>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.</p>\n'}, {'answer_id': 78866, 'author': 'Joel Spolsky', 'author_id': 4, 'author_profile': 'https://Stackoverflow.com/users/4', 'pm_score': 3, 'selected': True, 'text': '<p>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.</p>\n\n<p>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.</p>\n\n<p>Here\'s the VBA code... let me know if you have any problem converting this to COM:</p>\n\n<pre><code>Range("A1").Select\nnumRows = Range("A1").End(xlDown).Row\n\nWhile ActiveWindow.ScrollRow &lt; numRows\n    ActiveWindow.LargeScroll Down:=1\nWend\n\nFor Each x In ActiveSheet.HPageBreaks\n    Debug.Print x.Location.Row\nNext\n</code></pre>\n\n<p>This code made one simplifying assumption:</p>\n\n<ul>\n<li>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.</li>\n</ul>\n'}]
['https://Stackoverflow.com/questions/78053', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/14238/']