Avatar of Norma Posy
Norma Posy
Flag for United States of America asked on

Excel pagebreak from VB code

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.
Visual Basic Classic

Avatar of undefined
Last Comment
Norma Posy

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Éric Moreau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Norma Posy

ASKER
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 Posy

ASKER
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 Posy

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

The link you supplied was interesting reading, though.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck