Sign in to follow this  
Followers 0
NewPlaza

Excel - get row count after setting page break margins

11 posts in this topic

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.

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

It sure can!  Thanks alot for the help.

Share this post


Link to post
Share on other sites

:D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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