Retrieve the print area in Excel
Solved
icion
Posted messages
15
Status
Member
-
PierreR Posted messages 72 Status Member -
PierreR Posted messages 72 Status Member -
Hello,
I work in Visual Basic on Excel and I’d like to know how to retrieve the “coordinates” of the print area automatically created by Excel (after Print Preview, for example).
Here's what I mean: I have a list of materials with prices and I’d like to write a subtotal at the end of each page that will be printed.
I’ve found how to define the print area:
ActiveSheet.PageSetup.PrintArea = "A1:B2"
for example, but PrintArea has no Address property, and I’d like to know how to retrieve the print area, because even if I set it, if it doesn’t fit into the area that will actually be printed, it won’t help.
Thanks in advance for your help.
Configuration: Windows Vista Firefox 3.0.5
I work in Visual Basic on Excel and I’d like to know how to retrieve the “coordinates” of the print area automatically created by Excel (after Print Preview, for example).
Here's what I mean: I have a list of materials with prices and I’d like to write a subtotal at the end of each page that will be printed.
I’ve found how to define the print area:
ActiveSheet.PageSetup.PrintArea = "A1:B2"
for example, but PrintArea has no Address property, and I’d like to know how to retrieve the print area, because even if I set it, if it doesn’t fit into the area that will actually be printed, it won’t help.
Thanks in advance for your help.
Configuration: Windows Vista Firefox 3.0.5
17 answers
Hello
If you want to print a subtotal, you have to put it on your sheet because you will not have the option to send it to the printer between two pages and knowing the print range will not let you determine the end of a paper page.
--
Always zen
If you want to print a subtotal, you have to put it on your sheet because you will not have the option to send it to the printer between two pages and knowing the print range will not let you determine the end of a paper page.
--
Always zen
Exactly, I’d like to avoid doing it manually. Isn’t there a way for a macro instruction to place the sum in the right place? Like: I grab the address ("Z52" at random) of the bottom-right cell along the print area edge and the macro places "=SUM(blabla)" right above/inside that cell?
Hello
Of course it's doable, but if you find the algorithm that computes the bottom of each printed page while taking into account all the layout parameters, that could interest a few readers...
--
Always zen
Of course it's doable, but if you find the algorithm that computes the bottom of each printed page while taking into account all the layout parameters, that could interest a few readers...
--
Always zen
Isn’t there already a function that does it?
Exactly, that would be the algorithm I’m looking for, but I don’t see how to do it. Unless someone knows how to retrieve the coordinates of the printer’s printable area...
Otherwise I was thinking of doing it differently: by retrieving the page number of the cell. It seems there is a method/attribute that already exists but I can’t find it. Does anyone know?
Exactly, that would be the algorithm I’m looking for, but I don’t see how to do it. Unless someone knows how to retrieve the coordinates of the printer’s printable area...
Otherwise I was thinking of doing it differently: by retrieving the page number of the cell. It seems there is a method/attribute that already exists but I can’t find it. Does anyone know?
Hello,
response to your first question,
To know the print area it’s enough to reverse the function...
But if there are several pages I haven't found the page limitations.
See you later
--
Experience teaches more reliably than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-) (Confucius)
response to your first question,
To know the print area it’s enough to reverse the function...
LaPlage = ActiveSheet.PageSetup.PrintArea
But if there are several pages I haven't found the page limitations.
See you later
--
Experience teaches more reliably than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-) (Confucius)
Thank you but personally it doesn't work. :s
What type is the variable "LaPlage"? I tried with Range and it doesn't like it.
LaPlage.Address = ActiveSheet.PageSetup.PrintArea doesn't work either.
With a string type it doesn't display anything.
What type is the variable "LaPlage"? I tried with Range and it doesn't like it.
LaPlage.Address = ActiveSheet.PageSetup.PrintArea doesn't work either.
With a string type it doesn't display anything.
But no, in the range there is directly the address of the type $A$1:$G$30
--
Experience teaches more reliably than advice. (André Gide)
If you bang on a pot and it rings hollow, it’s not necessarily the pot that’s empty. ;-) (Confucius)
--
Experience teaches more reliably than advice. (André Gide)
If you bang on a pot and it rings hollow, it’s not necessarily the pot that’s empty. ;-) (Confucius)
It's weird; for me LaPlage = "" when I run the macro in step-by-step mode and I hover over LaPlage (LaPlage isn't declared, without "Option Explicit"). What type is it?
It’s of type String, but the print range must already be defined!!!
Otherwise, how to return a range that doesn’t exist yet.
--
Experience teaches more reliably than advice. (André Gide)
If you bang on a pot and it sounds hollow, it isn’t necessarily the pot that’s empty. ;-)(Confucius)
Otherwise, how to return a range that doesn’t exist yet.
--
Experience teaches more reliably than advice. (André Gide)
If you bang on a pot and it sounds hollow, it isn’t necessarily the pot that’s empty. ;-)(Confucius)
Ok, it works if we define a range first:
ActiveSheet.PageSetup.PrintArea = "a1:b6"
LaPlage = ActiveSheet.PageSetup.PrintArea
But what's the point of retrieving the address if I already entered it? :p
The print area seems to be defined when you click "Print Preview" but now the macro doesn't work with it (LaPlage returns ""), and it's with this that I would like it to work :s
ActiveSheet.PageSetup.PrintArea = "a1:b6"
LaPlage = ActiveSheet.PageSetup.PrintArea
But what's the point of retrieving the address if I already entered it? :p
The print area seems to be defined when you click "Print Preview" but now the macro doesn't work with it (LaPlage returns ""), and it's with this that I would like it to work :s
Well, we need to know what you want and see what you’re saying…!!!
I’m working in Visual Basic on Excel and I’d like to know how to retrieve the “coordinates” of the print area automatically created by Excel (after Print Preview, for example).
--
Experience teaches more reliably than advice. (André Gide)
If you’re banging at a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-) (Confucius)
I’m working in Visual Basic on Excel and I’d like to know how to retrieve the “coordinates” of the print area automatically created by Excel (after Print Preview, for example).
--
Experience teaches more reliably than advice. (André Gide)
If you’re banging at a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-) (Confucius)
Yes, I don’t see any contradiction with what I said...
If I do "Print Preview" and the macro:
LaPlage = ActiveSheet.PageSetup.PrintArea
LaPlage returns nothing. And it’s only the area created by "Print Preview" that I want to recover. (and it doesn’t work (and I don’t see the point of retrieving an address that you just typed...))
If I do "Print Preview" and the macro:
LaPlage = ActiveSheet.PageSetup.PrintArea
LaPlage returns nothing. And it’s only the area created by "Print Preview" that I want to recover. (and it doesn’t work (and I don’t see the point of retrieving an address that you just typed...))
It indeed doesn’t work with print previous, but with the command plage>> defined it works.
Sorry, I don’t see any other solution for the moment.
--
Experience teaches more reliably than advice. (André Gide)
If you bang on a pot and it sounds hollow, it isn’t necessarily the pot that’s empty. ;-) (Confucius)
Sorry, I don’t see any other solution for the moment.
--
Experience teaches more reliably than advice. (André Gide)
If you bang on a pot and it sounds hollow, it isn’t necessarily the pot that’s empty. ;-) (Confucius)
Yes, it annoys me that Excel developers didn't plan for that :(
Too bad, we'll fill it in by hand I think...
Thanks anyway for your answers.
Too bad, we'll fill it in by hand I think...
Thanks anyway for your answers.
I found on a site a function that returns the page number on which the cell is located.
This is enough to solve my problem. So I’m sharing it with you :D
------------------------
This function (adaptation by Frédéric Sigonneau) returns the
page number of the cell where it is called:
Function NumPage() As Integer
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak, HPB As HPageBreak
Dim Wksht As Worksheet, Cellule As Range
Dim Col As Integer, Ligne As Long
Application.Volatile
Set Cellule = Application.Caller
Set Wksht = Cellule.Worksheet
Ligne = Cellule.Row
Col = Cellule.Column
If Wksht.PageSetup.Order = xlDownThenOver Then
HPC = Wksht.HPageBreaks.Count + 1
VPC = 1
Else
VPC = Wksht.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1
For Each VPB In Wksht.VPageBreaks
If VPB.Location.Column > Col Then Exit For
NumPage = NumPage + HPC
Next VPB
For Each HPB In Wksht.HPageBreaks
If HPB.Location.Row > Ligne Then Exit For
NumPage = NumPage + VPC
Next HPB
End Function
Then, in A25 for example:
=NumPage()
------------------------
It's a quite heavy function (it takes more than 2 minutes for about 100 lines (I only tested it for 10 lines) and some say it doesn’t always work.
Otherwise I also have something much simpler, but which returns the total number of pages (so you need an empty workbook and run a macro with a loop adding lines and this function to get the page of the active cell).
ActiveWindow.SelectedSheets.HPageBreaks.Count + 1
(you need +1 otherwise one is missing... I don’t know why ^^)
example: macro that writes the page number in column A for 200 rows
Sub Page()
Dim i As Integer
For i = 1 To 200
Cells(i, 1) = ActiveWindow.SelectedSheets.HPageBreaks.Count + 1
Next i
End Sub
Hope this helps as many people as possible (besides me).
Have a nice day !! :)
This is enough to solve my problem. So I’m sharing it with you :D
------------------------
This function (adaptation by Frédéric Sigonneau) returns the
page number of the cell where it is called:
Function NumPage() As Integer
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak, HPB As HPageBreak
Dim Wksht As Worksheet, Cellule As Range
Dim Col As Integer, Ligne As Long
Application.Volatile
Set Cellule = Application.Caller
Set Wksht = Cellule.Worksheet
Ligne = Cellule.Row
Col = Cellule.Column
If Wksht.PageSetup.Order = xlDownThenOver Then
HPC = Wksht.HPageBreaks.Count + 1
VPC = 1
Else
VPC = Wksht.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1
For Each VPB In Wksht.VPageBreaks
If VPB.Location.Column > Col Then Exit For
NumPage = NumPage + HPC
Next VPB
For Each HPB In Wksht.HPageBreaks
If HPB.Location.Row > Ligne Then Exit For
NumPage = NumPage + VPC
Next HPB
End Function
Then, in A25 for example:
=NumPage()
------------------------
It's a quite heavy function (it takes more than 2 minutes for about 100 lines (I only tested it for 10 lines) and some say it doesn’t always work.
Otherwise I also have something much simpler, but which returns the total number of pages (so you need an empty workbook and run a macro with a loop adding lines and this function to get the page of the active cell).
ActiveWindow.SelectedSheets.HPageBreaks.Count + 1
(you need +1 otherwise one is missing... I don’t know why ^^)
example: macro that writes the page number in column A for 200 rows
Sub Page()
Dim i As Integer
For i = 1 To 200
Cells(i, 1) = ActiveWindow.SelectedSheets.HPageBreaks.Count + 1
Next i
End Sub
Hope this helps as many people as possible (besides me).
Have a nice day !! :)
Hello icion,
A big thank you to you because you found the question and the solution!
Thanks for sharing it with everyone: that's the forum's purpose.
Indeed, by using the properties of the editing area it should be feasible and there's certainly a way to simplify this function for your specific problem, and I will look into it if I have a bit of time.
--
Always zen
A big thank you to you because you found the question and the solution!
Thanks for sharing it with everyone: that's the forum's purpose.
Indeed, by using the properties of the editing area it should be feasible and there's certainly a way to simplify this function for your specific problem, and I will look into it if I have a bit of time.
--
Always zen