flyingboz Posted February 9, 2006 Share Posted February 9, 2006 Working on automatically inserting pagebreaks using .HPageBreaks.AddAll suggestions welcome. From http://www.mrexcel.com/td0032.html. I cribbed this little vbscriptSub 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 SubPorted most of it to au3 manually,but am choking on the .HPageBreaks.Add lineWith $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. Link to comment Share on other sites More sharing options...
randallc Posted February 10, 2006 Share Posted February 10, 2006 With $oXL.Application.ActiveWorkbook.ActiveSheet$lastval = .Cells('1,1').ValueFor $i = 1 to 10 $ThisRange=.Cells($i,1) $thisval = $ThisRange.Value If $thisval <> $lastval Then .HPageBreaks.Add($ThisRange) EndIf $lastval = $thisvalNextEndwithKeep at it!Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
flyingboz Posted February 10, 2006 Author Share Posted February 10, 2006 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. Link to comment Share on other sites More sharing options...
flyingboz Posted February 10, 2006 Author Share Posted February 10, 2006 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. Link to comment Share on other sites More sharing options...
flyingboz Posted February 10, 2006 Author Share Posted February 10, 2006 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. Link to comment Share on other sites More sharing options...
randallc Posted February 10, 2006 Share Posted February 10, 2006 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)^ ERRORBest, Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
flyingboz Posted February 10, 2006 Author Share Posted February 10, 2006 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. Link to comment Share on other sites More sharing options...
randallc Posted February 10, 2006 Share Posted February 10, 2006 2003Perhaps 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
flyingboz Posted February 11, 2006 Author Share Posted February 11, 2006 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. Reading the help file before you post... Not only will it make you look smarter, it will make you smarter. Link to comment Share on other sites More sharing options...
randallc Posted February 11, 2006 Share Posted February 11, 2006 Hi,or..$lastval =.Cells(1,1).Valueyou had as "'1,1'" ...?Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now