excel pagebreak add com

[flyingboz]

Working on automatically inserting pagebreaks using .HPageBreaks.Add

All suggestions welcome.

From http://www.mrexcel.com/td0032.html. I cribbed this little vbscript

Sub AddBreaks() 
    StartRow = 2    
    FinalRow = Range("A65536").End(xlUp).Row    
    LastVal = Cells(StartRow, 1).Value      
    For i = StartRow To FinalRow        
        ThisVal = Cells(i, 1).Value    
        If Not ThisVal = LastVal Then          
            ActiveSheet.HPageBreaks.Add before:=Cells(i, 1)     
        End If      
        LastVal = ThisVal   
    Next i
End Sub

Ported most of it to au3 manually,but am choking on the .HPageBreaks.Add line

With $XL.Application.ActiveWorkbook.ActiveSheet
$lastval = .Cells('1,1').Value
For $i = 1 to 10
    $thisval = $XL.Application.ActiveWorkbook.ActiveSheet.Cells($i,1).Value
    If $thisval <> $lastval Then 
 ;syntax failures below :)
    ;$XL.HPageBreaks.Add($XL.Application.ActiveWorkbook.ActiveSheet.Cells($i,1))
    ;$XL.ActiveWorkbook.Activesheet.HPageBreaks.Add($XL.Application.ActiveWorkbook.ActiveSheet.Cells($i,1))
    EndIf
    $lastval = $thisval
Next

[randallc]

With $oXL.Application.ActiveWorkbook.ActiveSheet

$lastval = .Cells('1,1').Value

For $i = 1 to 10

$ThisRange=.Cells($i,1)

$thisval = $ThisRange.Value

If $thisval <> $lastval Then

.HPageBreaks.Add($ThisRange)

EndIf

$lastval = $thisval

Next

Endwith

Keep at it!

Randall

[flyingboz]

Thanks for the reply - Unfortunately, the .HPageBreaks.Add method still fails ... sigh.

$XLSTemplate = "C:\test.xls"
If Not FileExists($XLSTemplate) Then Exit -99
$oXL = ObjGet($XLSTemplate)
With $oXL.Application.ActiveWorkbook.ActiveSheet
    $lastval = .Cells ('10,1').Value
    cwl('1:1',$lastval)
    For $i = 1 To 10
        $ThisRange = .Cells ($i, 1)
        $thisval = $ThisRange.Value
        If $thisval <> $lastval Then
            cwl ($i,$thisval, $lastval)
            .HPageBreaks.Add ($ThisRange)
        EndIf
        $lastval = $thisval
    Next
EndWith

Note: CWL() is my ConsoleWriteLine Helper function - Its output shows that the other methods called and properties used are returning expected results.

Any ideas are welcome -

[flyingboz]

This code successfully adds a HPageBreak - I create the

PageBreak object, and then call it with a defined range

object - I still am having problems calling it from within a loop.

Some concept I'm still not getting....

I'm desirous of having a UDF that would let me work w/

the Activesheet property, such that I could simply pass

the udf a range variable, i.e. _HPageBreakAdd($range)

Working snippet....

$XLSTemplate = "C:\test.xls"
$oXL = ObjGet($XLSTemplate)
If Not IsObj($oXL) Then Exit -33
            
            
$Range = $oXL.Worksheets(1).Cells(10,10)    
$PageBreak = $oXL.Worksheets(1).HPageBreaks
$PageBreak.Add($Range)

[flyingboz]

A working sample - not ideal, and likely not anywhere as good as a true excel guru could make it....

On my system, this puts a horizontal pagebreak every other line.

$XLSTemplate = "C:\test.xls"
$oXL = ObjGet($XLSTemplate)
If Not IsObj($oXL) Then Exit -33

$PageBreak = $oXL.Worksheets(1).HPageBreaks
For $i = 1 to 100 step 2
    If $i < $PageBreak.Count Then 
        $oXL.Worksheets(1).HPageBreaks($i).Location = $oXL.Worksheets(1).Range("E" & $i)
    Else
        $Range = $oXL.Worksheets(1).Cells($i,10)
        $oXL.Worksheets(1).HPageBreaks.Add($range)
    EndIf
Next

[randallc]

Hi,

StrangE!

Mine works for me, but yours gives me an error!

C:\Program Files\Au3PROGS\SciTe\XLBreakAdd3.au3 (20) : ==> The requested action with this object has failed.:

$oXL.ActiveSheet.HPageBreaks($i).Location = $oXL.ActiveSheet.Range("E" & $i)

$oXL.ActiveSheet.HPageBreaks($i).Location = $oXL.ActiveSheet.Range("E" & $i)^ ERROR

Best, Randall

[flyingboz]

Mine works for me, but yours gives me an error!

Randall -

What version of excel / windows are you running? I'm preparing to deploy across a userbase of w2k and XP workstations, primarily w/ office 2003, but some office 2000, so I would be very curious as to

what you, (and any others) might find as to working implementations, so I can build a matrix and case around any MS oddities.

Thanks again!!

[randallc]

2003

Perhaps my script would work if you use;

If $thisval <> $lastval and $i<>1 Then .HPageBreaks.Add($ThisRange)

and check the range for insert does not go beyond last row either!

Randall

[flyingboz]

aah....

Yes, I can see where trying to put a pagebreak before line 1 might be a problem Haven't tested yet,

but that is indeed the likely culprit.

[randallc]

Hi,

or..

$lastval =.Cells(1,1).Value

you had as "'1,1'" ...?

Randall

[Norm73]

I know this is very old, but if this code doesn't work for anyone:

$oExcel.ActiveSheet.HPageBreaks($nPage).Location = $oExcel.ActiveSheet.Range("A" & $iRow)

 

is there an alternative:

With $oWorkbook.Worksheets(1)
    .HPageBreaks.Add($oExcel.ActiveCell)
    .HPageBreaks.Add(.Range("A" & $iRow))
    .VPageBreaks.Add(.Range("A" & $iRow))
EndWith

 

[water]

You are correct. The thread is very, very old. It would have been better to create a new thread and - if needed - refer to this thread

I have been able to translate this VBA solution to AutoIt: 

#include <Excel.au3>

; XlDirection enumeration - Specifies the direction in which to move.
Global Const $xlUp = -4162 ; Up.
; XlPageBreak-Enumeration - Specifies page break location on the worksheet.
Global Const $xlPageBreakManual = -4135 ; Page breaks are manually inserted.

Global $oExcel, $oWorkbook, $oWorksheet, $iLastRow
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookAttach("Mappe1")  ; Change the name of the already opened workbook
_AddPageBreaks($oExcel, $oWorkbook.Sheets(1))
Exit

Func _AddPageBreaks($oExcel, $oWorksheet)
    Local $iLastRow = $oWorksheet.Cells($oWorksheet.Rows.Count, "A").End($xlUp).Row ; find last used row in column A
    For $iRow = 10 To $iLastRow Step 8
        $oWorksheet.HPageBreaks.Add($oWorksheet.Rows($iRow))
        $oWorksheet.Rows($iRow).PageBreak = $xlPageBreakManual
    Next
EndFunc   ;==>_AddPageBreaks

 

Edited February 23 by water