Excel HPageBreaks NOT working, using VBSCript
Posted: 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.
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