none
Excel HPageBreaks NOT working, using VBSCript RRS feed

  • Question

  • 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



    Krishnaprasad P.K

    Monday, May 28, 2018 3:17 PM

All replies

  • The part

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

    fixes the page setup and makes Excel ignore your HPageBreak. If you want to be able to set your own page breaks, you must remove that part of the code.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 28, 2018 3:47 PM
  • @Hans Vogelaar,

    Thanks for the valuable suggestion.

    But here i need to print everything in two pages.  By giving a page break at row 40

    I tried this by removing following codes

    With objSheet.PageSetup.Zoom = False .FitToPagesWide = 1

    .FitToPagesTall = 2 End With


     But i am getting the result in ten pages. How can i set this to two pages...


    Krishnaprasad P.K

    Monday, May 28, 2018 4:17 PM
  • If you want the result in two pages, you will have to let Excel set the page breaks automatically.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 28, 2018 4:45 PM
  • @Hans Vogelaar,

    Good Morning Hans Vogelaar..

    Once i removed all page break setting from my script i got the result in eight pages. I tried almost all the possible techniques ..... But still no luck.

    Actually i am trying to set values to the excel sheet after reading student data from other sources. 

    The data source is having student activities which they participated in last two years. (And the number of activities participated varies for each student  and i have to give a page break once i finished their first year details)

    If i pasted the entire code it will be more than 350 lines, hence i shorten the problem as given here. 

    Here is the updated code after removing page break settings.  Any other suggestions are welcome...

    	Option Explicit
    	Dim objExcel,objWorkbook,objSheet,objRange,intPageBreakRow,intRow,i
    	const xlPageBreakPreview  = &H2
    	Const xlPageLayoutView = 3
    	
    	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					 
    	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
    	
    	objExcel.Cells(intPageBreakRow+1, 2).Value = "Page2 This is the text that we want to wrap in column B"
    	
    	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.ActiveWindow.View = xlPageBreakPreview 			 			 			
    	objExcel.Rows.AutoFit				   
    					   
    	
    		

               

                           
        
            


    Krishnaprasad P.K


    • Edited by Krishnaprasad P.K Tuesday, May 29, 2018 2:48 AM Edited to give more details of problem.
    Tuesday, May 29, 2018 2:38 AM
  • Hello Krishnaprasad,

    What Hans said is that there is conflict between 

    .FitToPagesTall = 2
    
    and
    
    objSheet.HPageBreaks.Add objSheet.Rows(intPageBreakRow+1)

    So you could keep one of them for your need and delete another. There is no need to delete them all.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 29, 2018 5:21 AM
  • Hai,

    Thanks for the response. 

    Now I included line 

    objSheet.HPageBreaks.Add objSheet.Rows(intPageBreakRow+1)

    in the script but now output have ten pages... 

    Any other suggestions...


    Krishnaprasad P.K

    Tuesday, May 29, 2018 10:32 AM
  • Hello Krishnaprasa,

    Please try below setting.

            With objSheet.PageSetup
                  .Zoom = 45
     	      .FitToPagesWide = false
     	      .FitToPagesTall = false
            End With
    
            objSheet.HPageBreaks.Add objSheet.Rows(intPageBreakRow+1)

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 31, 2018 8:06 AM