Excel pagebreak from VB code

Norma Posy
Norma Posy used Ask the Experts™
on
My app creates an Excel spreadsheet.
There are places where a page break is needed.
Code snippet:

Dim oXLApp As Object
Dim oXLBook As Object
Dim oXLSheet As Object

Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)

If [condition is met] Then
   sRange = "A" & Trim(CStr(iRow))
   oXLSheet.HPagebreaks.Add Before:=oXLSheet.Range(sRange)
End If

Open in new window


I've stepped through the code, and [condition is met] is detected, and the if-then is executed. But nothing happens. No page break occurs.

Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
Norma PosyPhysicist

Author

Commented:
I'm working on it.
I will get back to you.
Meanwhile:
I was wondering if the sequence matters.
Maybe page breaks should be inserted AFTER the spreadsheet is populated.
I'm guessing that changing the contents of a cell might negate other attributes.
I'm going to experiment.
Norma PosyPhysicist

Author

Commented:
Here is a little test page break test.
Some numbers are placed in the first 6 rows of column A.
Then a page break is inserted after row 3:
Note: xl Constants are defined in a "constants" module.

Private Sub TestPageBreak_Click()
Dim iI As Integer
Dim sCellNumber As String
Dim oXLApp As Object
Dim oXLBook As Object
Dim oXLSheet As Object
Dim sWorkbookName As String
Dim sCurrentName As String
'
Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
sWorkbookName = "Testing.xls"
'
With oXLApp
    .Visible = True
    sCurrentName = .Workbooks.Add.Name
    With .Range("A1:F300")
        .Font.Name = "Courier New"
        .Font.Size = 10
    End With
    .Range("A1:A200").ColumnWidth = 5
    .Columns("A:A").Select
    .Selection.HorizontalAlignment = xlCenter
'
' Insert Cell Data
    For iI = 1 To 6
        sCellNumber = "A" & CStr(Trim(iI))
        .Range(sCellNumber).Value = iI
    Next iI
' Page Break
' (alternate try:)
'    oXLSheet.HPagebreaks.Add Before:=oXLSheet.Range("A4")
' using this one:
    oXLSheet.Rows(4).PageBreak = xlPageBreakManual
End With
'
Set oXLApp = Nothing
Set oXLBook = Nothing
Set oXLSheet = Nothing
End Sub

Open in new window


Two spreadsheets are created!
"Book1" Contains the page break
"Book2" Contains the data.

Please help.
Norma PosyPhysicist

Author

Commented:
I solved it myself.
Line 17 (code, above) is superfluous, and creates a second spreadsheet.

The link you supplied was interesting reading, though.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial