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 6 years and 5 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.
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.
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
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)
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.
@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..
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 6 years and 5 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.