Sign in to follow this  
Followers 0
flyingboz

excel pagebreak add com

10 posts in this topic

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

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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 -


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

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)

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

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

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!!


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

aah....

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

but that is indeed the likely culprit.


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0