Excel HPageBreaks NOT working, using VBSCript

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
krishkri
Posts: 9
Last visit: Wed May 30, 2018 7:24 pm

Excel HPageBreaks NOT working, using VBSCript

Post 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
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Excel HPageBreaks NOT working, using VBSCript

Post 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.
User avatar
krishkri
Posts: 9
Last visit: Wed May 30, 2018 7:24 pm

Re: Excel HPageBreaks NOT working, using VBSCript

Post 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)
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Excel HPageBreaks NOT working, using VBSCript

Post 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.
User avatar
krishkri
Posts: 9
Last visit: Wed May 30, 2018 7:24 pm

Re: Excel HPageBreaks NOT working, using VBSCript

Post 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.
User avatar
krishkri
Posts: 9
Last visit: Wed May 30, 2018 7:24 pm

Re: Excel HPageBreaks NOT working, using VBSCript

Post 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..
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Excel HPageBreaks NOT working, using VBSCript

Post 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.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked