Determining page numbers in Excel 97

[Anthony Ryan]

Hi there,

I have a Excel 97 workbook which contains some 187 visible worksheets of
various sizes (different amount of pages in length).
I have a macro that selects all the visible sheets. All these sheets have
a footer with the page number in it. When a sheet is activated within the
macro how do I determne the page number that the first page of the worksheet
is (similar to the seeing the page number when viewing the workbook in the
print preview)?

The code looks like this:

Dim intPageNo As Integer
Dim w as worksheet

For Each w in Worksheets
If w.Visible = True Then
w.Activate
intPageNo=?

etc, etc
End If
Next w

[Jim Cone]

Anthony,

I not quite sure exactly what you want, but this should get you started...

'-------------------------------------------------------------------------
Sub GetFirstPageOnSheet()
'Some sheets might be Chart sheets so object is used instead of Worksheet.
'ExecuteExcel4Macro("get.document(50)") from xl4 is the only tool available
'to readily count printable pages.
Dim EachPageCount As Long
Dim RunningTotal As Long
Dim TargetSheet As Object
Dim AllSheets As Sheets
Dim Sht As Object
Dim Msg As String

Set AllSheets = ActiveWindow.SelectedSheets
Set TargetSheet = ActiveSheet
Application.ScreenUpdating = False
For Each Sht In AllSheets
If Sht.Visible Then
Sht.Activate 'Activate required in order to get page count.
If Sht Is TargetSheet Then
EachPageCount = ExecuteExcel4Macro("get.document(50)")
If EachPageCount = 0 Then
Msg = " has no printable pages."
Else
Msg = " first page to print is " & RunningTotal + 1
End If
MsgBox Chr$(34) & Sht.Name & Chr$(34) & Msg, , " Find Printable
Pages"
Exit For
End If
EachPageCount = ExecuteExcel4Macro("get.document(50)")
RunningTotal = RunningTotal + EachPageCount
End If
Next 'Sht
AllSheets.Select
Application.ScreenUpdating = True
Set Sht = Nothing
Set TargetSheet = Nothing
Set AllSheets = Nothing

End Sub
'-------------------------------------------------------------------------

Regards

Jim Cone
San Jose, CA

**********************************************
"Anthony Ryan" <aryan2000@hotmail.com> wrote:
>Hi there,

>I have a Excel 97 workbook which contains some 187 visible worksheets of
>various sizes (different amount of pages in length).
>I have a macro that selects all the visible sheets. All these sheets have
>a footer with the page number in it. When a sheet is activated within the
>macro how do I determne the page number that the first page of the worksheet
>is (similar to the seeing the page number when viewing the workbook in the
>print preview)?
>The code looks like this:
>
>Dim intPageNo As Integer
>Dim w as worksheet
>
>For Each w in Worksheets
> If w.Visible = True Then
> w.Activate
> intPageNo=?
>
> etc, etc
> End If
>Next w