Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you get the range reference for each page in a worksheet print range?
Is there a way to determine the range for each of the
pages (by pages I mean the pages as they would print out) on a worksheet. I would like to build a new printrange based on printing only the pages I want to print. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you get the range reference for each page in a worksheet print range?
loop through the vertical and horizontal pagebreaks collections possibly.
-- Regards, Tom Ogilvy "Crosby" wrote in message ... Is there a way to determine the range for each of the pages (by pages I mean the pages as they would print out) on a worksheet. I would like to build a new printrange based on printing only the pages I want to print. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you get the range reference for each page in a worksheet print range?
Good idea. I am going to try that. I'm sure I can get the
row component of the page that way, but how about the column component. I assume if there is only one column of pages, there wouldn't be any vertical page breaks. -----Original Message----- loop through the vertical and horizontal pagebreaks collections possibly. -- Regards, Tom Ogilvy "Crosby" wrote in message ... Is there a way to determine the range for each of the pages (by pages I mean the pages as they would print out) on a worksheet. I would like to build a new printrange based on printing only the pages I want to print. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you get the range reference for each page in a worksheet print range?
You can use the UsedRange to determine both the horizontal and vertical
extent of what Excel considers to be in use. If you have set a printarea, then you can use that to determine the extent of your printarea. Yes, the pagebreak collections can be difficult to deal with. Also note that they (automatic pagebreaks) can change with a change in which printdriver is in effect. Here is some rough code that I have posted in the past which might give you a start. It uses some Excel4 macro commands because those are faster and more dependable: Here is a method to get an array of horizontal pagebreaks and vertical pagebreaks. The horizontal pagebreaks are a list of rows that have the pagebreak and vertical a list of column numbers: Sub Tester1() Dim horzpbArray() Dim verpbArray() ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) Debug.Print J, horzpbArray(J) Next J i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For J = LBound(verpbArray, 1) To UBound(verpbArray, 1) Debug.Print J, verpbArray(J) Next J End Sub This uses an Excel 4 macro to get this information. This is much faster than the VBA pagebreak which uses the printer driver and can be very slow. The is a pagebreak property of the range. It can be tested to see if a pagebreak exists if rows(6).pagebreak = xlNone then 'No pagebreak Else ' Has pagebreak if rows(6).pagebreak = xlPageBreakAutomatic then 'Automatic pagebreak elseif rows(6).pagebreak = xlPageBreakManual then ' Manual pagebreak End if End if Combining the above gives: Sub Tester1() Dim horzpbArray() Dim verpbArray() Dim brkType As String ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) If Rows(horzpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, horzpbArray(j), brkType Next j i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For j = LBound(verpbArray, 1) To UBound(verpbArray, 1) If Columns(verpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, verpbArray(j), brkType Next j End Sub Sample Output: Horizontal Pagebreaks (rows): 1 13 Manual 2 24 Manual 3 39 Manual 4 67 Manual 5 87 Manual 6 114 Automatic Vertical Pagebreaks (columns): 1 2 Manual 2 6 Automatic This should get you started. Regards, Tom Ogilvy wrote in message ... Good idea. I am going to try that. I'm sure I can get the row component of the page that way, but how about the column component. I assume if there is only one column of pages, there wouldn't be any vertical page breaks. -----Original Message----- loop through the vertical and horizontal pagebreaks collections possibly. -- Regards, Tom Ogilvy "Crosby" wrote in message ... Is there a way to determine the range for each of the pages (by pages I mean the pages as they would print out) on a worksheet. I would like to build a new printrange based on printing only the pages I want to print. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you name a worksheet by refering to a cell reference or range | Excel Discussion (Misc queries) | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Print selected range in a worksheet | Excel Discussion (Misc queries) | |||
Macro to print a selected range, not entire worksheet | Excel Discussion (Misc queries) | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions |