NewPlaza 4 Posted June 22, 2013 Share Posted June 22, 2013 Hello, How can I get the number of rows in a given page break. At first, I had the number of rows set and all worked great until I ran my script on a second workstation and found that the row count was different per page break (possibly due to the printer and/or paper type setting.). So, can I find out how many rows are on a given pagebreak? Thanks. Link to post Share on other sites
water 2,720 Posted June 22, 2013 Share Posted June 22, 2013 With the HPageBreak property of the Worksheet object you can retrieve a list of page breaks. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
NewPlaza 4 Posted June 22, 2013 Author Share Posted June 22, 2013 Thank you for the reply water. Will this only list how many pages a given spreadsheet will have? I don't care how many pages will need to be printed. One, five, fifty, etc... Doesnt matter. What does matter is the information must be aligned correctly accordingly to fit each page. So far, I have seen row count at 60, 62, and 64 per page and likely to change on yet another computer/printer configuration. Link to post Share on other sites
water 2,720 Posted June 22, 2013 Share Posted June 22, 2013 No. It returns a collection of page breaks. You can loop through this collection and get the location of every break. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
NewPlaza 4 Posted June 23, 2013 Author Share Posted June 23, 2013 I'm having a hard time converting the information from the two sources you provided to usable code. Could you provide an example if possible. I would really appreciate it. Thanks. Link to post Share on other sites
water 2,720 Posted June 23, 2013 Share Posted June 23, 2013 I will as soon as I return to my windows PC. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
Solution water 2,720 Posted June 23, 2013 Solution Share Posted June 23, 2013 This example code returns all horizontal page breaks for the active worksheet: #include <Excel Rewrite.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xls") $oHPageBreaks = $oWorkbook.ActiveSheet.HPageBreaks For $i = 1 To $oHPageBreaks.Count $oHPageBreak = $oHPageBreaks.Item($i) If @error Then ExitLoop ConsoleWrite($oHPageBreak.Location.Row & @LF) Next My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
NewPlaza 4 Posted June 23, 2013 Author Share Posted June 23, 2013 Water, I made a few modifications from the example you provided. Here is my code. Local $oExcel = ObjCreate("Excel.Application") Local $oWorkbook = $oExcel.WorkBooks.Add() $oExcel.Activesheet.Cells(100, 1).Value = "SomeValue" With $oWorkbook.ActiveSheet.PageSetup .LeftMargin = $oWorkbook.Application.InchesToPoints(0.0) .RightMargin = $oWorkbook.Application.InchesToPoints(0.0) .TopMargin = $oWorkbook.Application.InchesToPoints(0.0) .BottomMargin = $oWorkbook.Application.InchesToPoints(0.0) .HeaderMargin = $oWorkbook.Application.InchesToPoints(0.0) .FooterMargin = $oWorkbook.Application.InchesToPoints(0.0) EndWith $oHPageBreaks = $oWorkbook.ActiveSheet.HPageBreaks For $i = 1 To $oHPageBreaks.Count $oHPageBreak = $oHPageBreaks.Item($i) If @error Then ExitLoop Next Local $iPageBreak = $oHPageBreak.Location.Row - 1 ConsoleWrite($iPageBreak & @LF) $oExcel.Application.DisplayAlerts = 0; * 0=Do not display alerts. $oExcel.Application.Quit() Thank you again for all your help. Link to post Share on other sites
water 2,720 Posted June 24, 2013 Share Posted June 24, 2013 Great that your problem could be solved. BTW: Couldn't .LeftMargin = $oWorkbook.Application.InchesToPoints(0.0) be reduced to .LeftMargin = 0 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
NewPlaza 4 Posted June 27, 2013 Author Share Posted June 27, 2013 It sure can! Thanks alot for the help. Link to post Share on other sites
water 2,720 Posted June 27, 2013 Share Posted June 27, 2013 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
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