Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How can I get the number of a printed page for use in a cell?

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.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,203
Default How can I get the number of a printed page for use in a cell?

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" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11
Default How can I get the number of a printed page for use in a cell?

On Sun, 31 May 2009 19:43:01 -0700, JLatham
wrote:

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


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" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,203
Default How can I get the number of a printed page for use in a cell?

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.

"Mark F" wrote:

On Sun, 31 May 2009 19:43:01 -0700, JLatham
wrote:

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


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" wrote:

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to display current page number in any cell of that page. Laljeet Excel Discussion (Misc queries) 8 February 2nd 08 02:31 AM
Configuring the number of rows printed per page BK-DWS-NJ Excel Discussion (Misc queries) 3 July 20th 07 05:04 PM
Increment a number in a cell for each page printed Steve Vincent Excel Discussion (Misc queries) 6 June 19th 06 11:12 PM
New form number for each page printed jimqual7 Excel Discussion (Misc queries) 6 March 20th 06 05:24 PM
sheet tabs as page number and in a cell page of pages? [email protected] Excel Discussion (Misc queries) 0 November 22nd 05 03:43 PM


All times are GMT +1. The time now is 08:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"