title: Adding pagebreaks via VBA doesn't work
body: <p>I created code to set the pagebreaks in an excel report to deal with the orphan issue (i.e. one line of text spills over onto the next page, etc.). The code works fine when I run it with the report open / visible.</p>
  
  <p>It is part of a larger application which is opened and the code executed from MS Access. Excel is not visible during the process to improve performance.</p>
  
  <p><strong>When I run my code from MS Access it no longer works... it doesn't produce an error, but simply ignores the actual pagebreak setting command.</strong></p>
  
  <p>I read on various <a href="http://www.ozgrid.com/forum/showthread.php?t=65887" rel="nofollow">forums</a> that in order to avoid this problem, excel needs to be first switched over to <code>ActiveWindow.View = xlPageBreakPreview</code>, but that doesn't work either (I suspect since Excel isn't visible).</p>
  
  <p>I have tested for the following:</p>
  
  <ul>
  <li>Code works when it is started manually or stepped through with F8</li>
  <li>Code is executed when called upon from Access (I set breakpoints)</li>
  <li>Switching the window view doesn't do anything either</li>
  </ul>
  
  <p><strong>How can I get Excel to change the pagebreaks via code when Excel is run in the background?</strong></p>
  
  <p>This is my code:</p>
  
  <pre><code>Sub TheOrphanProblem()
  Dim iPageBrkRow
  
  'Determine if there are page breaks and if so on which row of the document
  If FindNthAutoPageBreak(wsRptHolding, 1) Is Nothing Then
      'No pagebreak found so we exit the sub
      Exit Sub
  Else
      iPageBrkRow = FindNthAutoPageBreak(wsRptHolding, 1).Row 'Get row
  End If
  
  Debug.Print iPageBrkRow
  
  Dim x As Integer
  Dim sCase As String
  Dim rNewposition As Range
  With wsRptHolding
  </code></pre>
  
  <p>'Code edited out for brevity. This part checks if there is an orphan problem and finds the new position for a pagebreak if needed. 
  It then provides that position as a range called "rNewposition".</p>
  
  <pre><code>'Moves page break to calculated position
  ActiveWindow.View = xlPageBreakPreview
  .HPageBreaks.Add rNewposition
  ActiveWindow.View = xlNormalView
  End With
  
  End Sub
  </code></pre>
  
  <p>This is the code I use to find the pagebreak positions...</p>
  
  <pre><code>Private Function FindNthAutoPageBreak(Sht As Worksheet, Nth As Long) As Range
  'Set page break of the last page so that sub asset groups are kept together
  
  Dim HP As HPageBreak
  Dim Ctr As Long
  For Each HP In Sht.HPageBreaks
      If HP.Type = xlPageBreakAutomatic Then
          Ctr = Ctr + 1
          If Ctr = Nth Then
              Set FindNthAutoPageBreak = HP.Location
          End If
      End If
  Next HP
  
  End Function
  </code></pre>