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