Determining page numbers in Excel 97


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Determining page numbers in Excel 97

  1. #1
    Anthony Ryan Guest

    Determining page numbers in Excel 97


    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





  2. #2
    Jim Cone Guest

    Re: Determining page numbers in Excel 97


    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center