Page 1 of 1

Excel HPageBreaks NOT working, using VBSCript

Posted: Mon May 28, 2018 12:19 am
by krishkri
I am trying to set Excel page breaks after row number 40 but its appearing on row number: 45. I removed all default page breaks to set new pagebreaks. The output excel file should be of two pages.

Can someone tell me the mistake which i created. Your Help will be greatly appreciated. (I tried to solve this from last 6 hours but no luck :) )

This code is part of my assignment, so i am trying this without changing the column width.

Code: Select all

Option Explicit
Dim objExcel,objWorkbook,objSheet,objRange,intPageBreakRow,intRow,i
const xlPageBreakPreview  = &H2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objSheet = objWorkbook.Worksheets(1)

Set objRange = objExcel.Range("B:F")
objRange.WrapText = TRUE

objSheet.Range("A:A").ColumnWidth = 1
objSheet.Range("B:B").ColumnWidth = 25
objSheet.Range("C:C").ColumnWidth = 25
objSheet.Range("D:D").ColumnWidth = 45
objSheet.Range("E:E").ColumnWidth = 14
objSheet.Range("F:F").ColumnWidth = 20

objSheet.DisplayAutomaticPageBreaks = False

With objSheet.PageSetup
 .Zoom = False
 .FitToPagesWide = 1
 .FitToPagesTall = 2
End With
objSheet.PageSetup.PrintArea = ""
objSheet.ResetAllPageBreaks  

intPageBreakRow=40
intRow=90

objExcel.Cells(1, 1).Value = "Page1 This is the text that we want to wrap in column A."
objExcel.Cells(2, 2).Value = "Page1 This is the text that we want to wrap in column B."
objExcel.Cells(3, 3).Value = "Page1 This is the text that we want to wrap in column C."
objExcel.Cells(5, 5).Value = "Page1 This is the text that we want to wrap in column E."
objExcel.Cells(6, 6).Value = "Page1 This is the text that we want to wrap in column F."

For i=2 To intPageBreakRow
    objExcel.Cells(i, 4).Value = "Page1 This is the text that we want to wrap in column D."
Next

objSheet.Range("F72").Value = "Page2 Some text aligned to the center"
objSheet.Range("F72").WrapText = True

For i=intPageBreakRow+1 To intRow
    objExcel.Cells(i, 4).Value = "Page2 This is the text that we want to wrap in column D."
Next

'objExcel.Range("A1: F"&intRow).Select  
objExcel.ActiveSheet.PageSetup.PrintArea="A1: F"&intRow
objSheet.ResetAllPageBreaks

'objExcel.Rows(intPageBreakRow+1).Select        
objSheet.HPageBreaks.Add objSheet.Rows(intPageBreakRow+1)

objExcel.ActiveWindow.View = xlPageBreakPreview                                     
objExcel.Rows.AutoFit

Re: Excel HPageBreaks NOT working, using VBSCript

Posted: Mon May 28, 2018 6:04 am
by jvierra
It is working if you choose the correct view:

Dim xlPageLayoutView : xlPageLayoutView = 3
objExcel.ActiveWindow.View = xlPageLayoutView


PageBreakView only shows the auto break settings. "Page Layout" shows the actual results of the manual breaks.

Re: Excel HPageBreaks NOT working, using VBSCript

Posted: Mon May 28, 2018 7:17 am
by krishkri
@jvierra
Thanks for the response
I made the necessary changes as you mentioned. But still the pages break apprears on Row 45 instead of Row 40.
Please let me know if you have any suggestions.. (Hint: I am using Excel 2007)

Re: Excel HPageBreaks NOT working, using VBSCript

Posted: Mon May 28, 2018 7:21 am
by jvierra
It doesn't in the page layout view and will print correctly. The pagebreakview does not reflect the applied settings. It reflects the paper to page defaults.

To learn and understand advanced Office programming you would be better to post questions in the Excel/Office Developer Forum.

Re: Excel HPageBreaks NOT working, using VBSCript

Posted: Mon May 28, 2018 8:07 am
by krishkri
@jvierra

I tried to print the document it still not giving break at row number 40.
So i will post this in other forums..
Thanks for your valuable suggestions.

Re: Excel HPageBreaks NOT working, using VBSCript

Posted: Mon May 28, 2018 8:08 am
by krishkri
@jvierra
Thanks for your valuable response...
I tried to print the document it still not giving break at row number 40.
So i will post this in other forums..

Re: Excel HPageBreaks NOT working, using VBSCript

Posted: Mon May 28, 2018 8:31 am
by jvierra
The issue is that your formatting does not fit the paper you are using. This breaks the pagination.

We usually create a template that applies all formatting then just fill the cells in script. This reduces the code complexity and allows for flexible formatting independent of code.