Excel - get row count after setting page break margins

[NewPlaza 4]

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.

[water 2,720]

With the HPageBreak property of the Worksheet object you can retrieve a list of page breaks.

[NewPlaza 4]

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.

[water 2,720]

No. It returns a collection of page breaks. You can loop through this collection and get the location of every break.

[NewPlaza 4]

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.

[water 2,720]

I will as soon as I return to my windows PC.

[water 2,720]

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

[NewPlaza 4]

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.

[water 2,720]

Great that your problem could be solved.

BTW: Couldn't

.LeftMargin = $oWorkbook.Application.InchesToPoints(0.0)

be reduced to

.LeftMargin = 0

[NewPlaza 4]

It sure can!  Thanks alot for the help.

[water 2,720]