Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to display current page number in any cell of that page. | Excel Discussion (Misc queries) | |||
Configuring the number of rows printed per page | Excel Discussion (Misc queries) | |||
Increment a number in a cell for each page printed | Excel Discussion (Misc queries) | |||
New form number for each page printed | Excel Discussion (Misc queries) | |||
sheet tabs as page number and in a cell page of pages? | Excel Discussion (Misc queries) |