Number of current page in Excel sheet?

[1005ultra]

Sorry, help me please... how can I get number of current page in sheet?

I find "pn = Selection.Information(wdActiveEndPageNumber)" in VBA.

But I do not know(find) this function in Autoit.

Thank you!

[footswitch]

Looking for the same thing...

One thing I know is

Selection.Information(wdActiveEndPageNumber) is for WORD, not Excel.

[Country73]

Depending on what all you are doing with your AutoIt script, you can include the Excel.au3

_ExcelSheetList() - Returns a list of all sheets in workbook, by name, as an array

_ExcelSheetNameGet() - Returns the name of the active sheet

[JoHanatCent]

Would HPageBreaks not help?

[footswitch]

One solution:

This finds the current page by first "guessing" the page number (current row / average of rows per page) and then fine-searching up or down until it finds the last page break before the current row.

Caveats:

1. The HPageBreaks Property must be correctly populated. The only way I know how to do that is change to Layout View (which forces HPageBreaks to always be up to date with your work).

2. If you select a cell beyond the "last page" (where there are no more page breaks, because no cell or space is occupied yet), it will simply return the last page. So, if you're in doubt, temporarily fill the selected cell or one below it with some text in order for Excel to create any necessary page breaks.

expandcollapse popup

; AutoIt v3.3.6.1
; www.autoitscript.com
#include <Math.au3>
#include <Excel.au3>
$oExcel=_ExcelBookNew()
; change to Layout View, this will always force the creation of page breaks where needed:
$oExcel.ActiveWindow.View = 3 ; equivalent to ActiveWindow.View = xlPageLayoutView
; from MSDN - Excel Object Model Reference - xlWindowView Enumeration
; write some cells to trigger the creation of page breaks (R1C1 coordinates):
_ExcelWriteCell($oExcel,"writing 3 3",3,3)
_ExcelWriteCell($oExcel,"writing 100 2",100,2)
_ExcelWriteCell($oExcel,"writing 900 4",900,4)
; go somewhere in the sheet (R1C1 coordinates):
$oExcel.Cells(53,3).Select
; every five seconds there will be a tray tip showing the detected page number
While 1
TrayTip("Current page",__ExcelGetPage($oExcel.ActiveSheet, $oExcel.Selection),5)
Sleep(5000)
WEnd
Func __ExcelGetPage($oWorksheet,$oRange) ; Worksheet Object, and Range Object. Can be given as $oExcel.ActiveSheet and $oExcel.Selection
$pagebreaks=$oWorksheet.HPageBreaks.Count
ConsoleWrite("Total page breaks: " & $pagebreaks & @CRLF) ; debug line
If $pagebreaks = 0 Then Return 1
$cur_row = $oRange.Row
ConsoleWrite("Selected row: " & $cur_row & @CRLF) ; debug line
$tmp_rows=$oWorksheet.HPageBreaks($pagebreaks).Location.Row
ConsoleWrite("Last page break row: " & $tmp_rows & @CRLF) ; debug line
If $pagebreaks = 1 Then
  If $cur_row < $tmp_rows Then
   Return 1
  Else
   Return 2
  EndIf
EndIf
$rows_per_page_estimate=$tmp_rows/$pagebreaks
$curpage = _Max(_Min(Round(($cur_row / $rows_per_page_estimate) + 1), $pagebreaks), 2)
; the same as:
;$curpage = Round(($cur_row / $rows_per_page_estimate) + 1)
;$curpage = _Min($curpage, $pagecount - 1)
;$curpage = _Max($curpage, 2)
; (has to be between 2 and pagecount - 1, or else the script will break out of bounds)
ConsoleWrite("Guessing page initially: " & $curpage & @CRLF) ; debug line
While 1
  If $oWorksheet.HPageBreaks($curpage).Location.Row = $cur_row Then
   ConsoleWrite("Current row is the same as the page break row of the guessed page"&@CRLF) ; debug line
   Return $curpage + 1
  ElseIf $oWorksheet.HPageBreaks($curpage).Location.Row < $cur_row Then
   ; meaning, the row of the page break of the guessed page is lower than the selected row
   ConsoleWrite("We're low in the guess "&$curpage&@CRLF) ; debug line
   If $curpage = $pagebreaks Then
    ; warning: even if the selected row is beyond the last page,
    ; we're always returning the last page as the result.
    ; in that specific case it should return error / undefined instead.
    Return $pagebreaks + 1
   Else
    $curpage += 1
   EndIf
   ConsoleWrite("Guessing "&$curpage&@CRLF) ; debug line
  ElseIf $oWorksheet.HPageBreaks($curpage).Location.Row > $cur_row Then
   If $oWorksheet.HPageBreaks($curpage - 1).Location.Row <= $cur_row Then
    ; meaning, the currently selected row is between the guessed page's break and the previous page break
    Return $curpage
   EndIf
   ConsoleWrite("We're high in the guess "&$curpage&@CRLF) ; debug line
   If $curpage = 2 Then
    Return 1
   Else
    $curpage -= 1
   EndIf
   ConsoleWrite("Guessing "&$curpage&@CRLF) ; debug line
  Else
   ConsoleWrite("UNEXPECTED IF-ELSE RESULT IN __ExcelGetCurrentPage()"&@CRLF) ; debug line
  EndIf
WEnd
EndFunc

Edited December 28, 2011 by footswitch