How can I get the number of a printed page for use in a cell?

[Mark F]

How can I get the number for a printed page for use in a
cell?

I want to make a table of contents that has the page
number that the first page of each sheet when it is
printed.

The number of pages for each sheet varies depending
on the page length used, so I don't see how to get
the page numbers unless do a Print Preview, look at the
page numbers in the print footers, and then edit the
table of contents page.

Is there a function to get the page that a cell prints
on in the print preview? This would allow me to do a Print
Preview and have the page number be inserted on each reference
page, which I then could use in the contents page.

Thank you.

 

[JLatham]

Take a look at this page at Ozgrid:
http://www.ozgrid.com/VBA/printed-pages.htm
I found that the first routine:

Sub PrintedPages()
MsgBox ExecuteExcel4Macro("Get.Document(50)")
End Sub

seems to work properly, while the second Sub returned 42 for a sheet that
the routine above returned 29 for. 29 was the correct answer as verified in
print preview. OOPS - subsequent runs also returned 42, which is not correct
but is understandable: the sheet is 14 pages long, divided into 3 sections:
A:E, F:R and then just S and T. BUT the area containing S and T is only 1
sheet 'long' so the answer should be 14 x 2 + 1 = 29, but it's being
calculated as 14 * 3 =42.

The result the code returns could be usable if your sheet is one page wide,
or if it is several sheets wide, all are of the same length. But getting the
results to you in a way you can use could be interesting.

If you want to pursue it, I'm willing to play with it some - or someone
who's already been down this road may come along and give us THE answer.

 

[Mark F]

Take a look at this page at Ozgrid:
http://www.ozgrid.com/VBA/printed-pages.htm
I found that the first routine:

Sub PrintedPages()
MsgBox ExecuteExcel4Macro("Get.Document(50)")
End Sub

If I read things correctly this is the function in your reference:
Sub HowManyPagesBreaks()
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer

iHpBreaks = ActiveSheet.HPageBreaks.Count + 1
iVBreaks = ActiveSheet.VPageBreaks.Count + 1
iTotPages = iHpBreaks * iVBreaks
MsgBox "This sheet will require " & iTotPages & _
" page(s) to print", vbInformation, "OzGrid.com"
End Sub

I don't know VBA or the ActiveSheet functions, but it seems like
I'd have to add code to:
1. iterate over all of the sheets (which assumes
that there is a function to either run through all of the sheets or
return the name of each sheet in turn)
2. handle the difference between actually printed pages and "grid"
possible pages that you mention below
and
3. Page breaks that aren't explicit, but depend on the
actual printer lines per page.

Is there a good reference that defines the built in structures
and functions that are available in Microsoft Excel 2003? (I've tried
looking "ActiveSheet" using Google but I can't find a definition;
my problem being that I don't even know the correct name to give the
thing. ( {structure, function} in {Excel, VBA} for example.)

Thanks

 

[JLatham]

Baby Goats on a Hang Glider and stuff hitting the fan!!

I just wrote a boatload of words and code and the damned system threw it
away and said it was too busy to deal with actually helping provide you with
more help! Arrrrrggggghhhhh!

Best way to assure solid communication: get in touch with me via email
(remove spaces) through
HelpFrom @ jlathamsite .com

Too tired and frustrated to try to put it all back together again.