Excel 2003: Number of pages as cell's content?

[Kimed]

Hi,

I have an application that generates an Excel spreadsheet of varying length as a solid body with "Rows to repeat at top" marked as a header for multiple pages. It needs a number of current page and total amount of pages printed. Now, I know about a standard Excel header/footer feature in Page Setup where you can write "&[Page] of &[Pages]"... problem is, bosses won't accept it. Both numbers have to be explicitly present in sheet's cells, and even if I somehow calculate the total amount of pages from the amount of lines, I have no idea how to make the same "Current page" cell from header band produce different values on different pages. Is there a way to get those numbers as a function, or I have to re-tailor the app to manually insert the header band into each page just because of this dumb requirement?

Thanks.

 

[xlhelp]

You need to define some names in your workbook.

First define Row number after page break

Name: RAPB
Formula: =GET.DOCUMENT(64)

Next get total page count

Name: TP
Formula: =GET.DOCUMENT(50)

Get Page of Pages
Name: TOTP
Formula: ="Page "&IF(ISNA(MATCH(ROW(),RAPB,1)),1,MATCH(ROW(),RAPB,1)+1)&" of " & TP + 0*now()

Get Page number of This Page
Name: THISP
Formula: =IF(ISNA(MATCH(ROW(),RAPB,1)),1,MATCH(ROW(),RAPB,1)+1)

So to get the Page 1 of 5 in a cell, simply use this formula:

=TOTP

and to get the current page:

=THISP



Avoid Hangovers. Stay drunk.

 

[Kimed]

I did as you said. While the TP name correctly calculates the amount of pages, THISP displays the same value 2 on every page. (was the "+1" in the formula redundant?)

Maybe I didn't explain my situation properly. The current page number (and total amount of pages) are not placed somewhere in the main body of cells regularly to match pages (where it indeed works), but just once in a band of rows marked as "Rows to repeat at top" in Page Setup. Being placed there, the cell with a formula =THISP doesn't produce varying results for varying pages printed.

 

[combo]

Actually, THISP displays the number of horizontal line of printed pages. Adding new name CAPB with formula =GET.DOCUMENT(64) and name THISC with formula =IF(ISNA(MATCH(Column(),RAPB,1)),1,MATCH(COLUMN(),RAPB,1)+1) you should get the number of vertical line of printed pages. Now mixing both you can get the current page, this depend on pagination order. It can be picked in names too (=GET.DOCUMENT(61), returns 1 for down then over and 2 for over then down).





combo

 

[xlhelp]

There is really no way to do what you want. When you repeat rows, you are embedding exactly the same data on every page. Each page needs to contain the formula. BTW, TOTP and THISP are what should be used. Both work just fine for me. JE McGimpsey has a VBA solution (Google it if you wish) but that also involves putting the UDF on every page.

There is an automatic page numbering macro that I came across. I will post it below. It still will not give you the right result in repeated rows. A word of caution, you will need to run this macro on a copy of the worksheet and NOT the worksheet itself. The reason is that the macro changes the worksheet once you run it.

I have personally NOT tested the macro.

Public Function PageNumber( _
Optional ByRef rng As Excel.Range) As Variant
Dim pbHorizontal As HPageBreak
Dim pbVertical As VPageBreak
Dim nHorizontalPageBreaks As Long
Dim nVerticalPageBreaks As Long
Dim nPageNumber As Long

On Error GoTo ErrHandler
Application.Volatile
If rng Is Nothing Then _
Set rng = Application.Caller
With rng
If .Parent.PageSetup.Order = xlDownThenOver Then
nHorizontalPageBreaks = .Parent.HPageBreaks.Count + 1
nVerticalPageBreaks = 1
Else
nHorizontalPageBreaks = 1
nVerticalPageBreaks = .Parent.VPageBreaks.Count + 1
End If
nPageNumber = 1
For Each pbHorizontal In .Parent.HPageBreaks
If pbHorizontal.Location.Row > .Row Then Exit For
nPageNumber = nPageNumber + nVerticalPageBreaks
Next pbHorizontal
For Each pbVertical In .Parent.VPageBreaks
If pbVertical.Location.Column > .Column Then Exit For
nPageNumber = nPageNumber + nHorizontalPageBreaks
Next pbVertical
End With
PageNumber = nPageNumber
ResumeHere:
Exit Function
ErrHandler:
'Could use much more error handling...!
PageNumber = CVErr(xlErrRef)
Resume ResumeHere
End Function


Avoid Hangovers. Stay drunk.