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.
Locked
User avatar
krishkri
Posts: 9
Joined: Thu Dec 10, 2009 5:02 pm

Excel HPageBreaks NOT working, using VBSCript

Post by krishkri » Mon May 28, 2018 12:19 am

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

User avatar
jvierra
Posts: 12564
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Excel HPageBreaks NOT working, using VBSCript

Post by jvierra » Mon May 28, 2018 6:04 am

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
Joined: Thu Dec 10, 2009 5:02 pm

Re: Excel HPageBreaks NOT working, using VBSCript

Post by krishkri » Mon May 28, 2018 7:17 am

@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)

User avatar
jvierra
Posts: 12564
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Excel HPageBreaks NOT working, using VBSCript

Post by jvierra » Mon May 28, 2018 7:21 am

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
Joined: Thu Dec 10, 2009 5:02 pm

Re: Excel HPageBreaks NOT working, using VBSCript

Post by krishkri » Mon May 28, 2018 8:07 am

@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
Joined: Thu Dec 10, 2009 5:02 pm

Re: Excel HPageBreaks NOT working, using VBSCript

Post by krishkri » Mon May 28, 2018 8:08 am

@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..

User avatar
jvierra
Posts: 12564
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Excel HPageBreaks NOT working, using VBSCript

Post by jvierra » Mon May 28, 2018 8:31 am

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.

Locked