Excel 2007 print problem
in [Programming]
|
Prev: How to use "notify" callback with winmm.dll
Next: how to use modules so that values calculated in one of them can be used by another?
From: Graham H on 16 Sep 2009 03:13 Using a procedure which was originally created in Excel 2002 I am getting an error at the following line when running the same procedure on Excel 2007. ActiveSheet.PageSetup.PrintArea = Selection.Address I am at a loss as to why it comes out at this point and would value any guidance. Graham
From: papou on 16 Sep 2009 07:15 Hello Graham There's obviously some hiccup with Selection.Address in your code. What is the error message? Does your selection contain any object? Try and get what Selection.Address actually returns, for instance: Debug.Print Selection.address or Msgbox Selection.Address Cordially Pascal "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de news: %23Z8MP0pNKHA.3384(a)TK2MSFTNGP04.phx.gbl... > Using a procedure which was originally created in Excel 2002 I am getting > an error at the following line when running the same procedure on Excel > 2007. > > ActiveSheet.PageSetup.PrintArea = Selection.Address > > I am at a loss as to why it comes out at this point and would value any > guidance. > > Graham
From: Graham H on 16 Sep 2009 09:30 Hi Pascal, Thanks for reply. This occurs when the procedure with a de-bug window which says 'PrintArea' of object 'PageSetup' failed. As I said it works perfectly fine in Exccel 2002, I just wondered what modification was required for 2007.The code it is included in is ActiveWindow.SelectedSheets.PrintOut Copies:=1 Cells.Select ActiveSheet.PageSetup.PrintArea = Selection.Address Range("A1").Select Sheets("Commands").Select Thanks for your comments. Graham papou wrote: > Hello Graham > > There's obviously some hiccup with Selection.Address in your code. > What is the error message? > Does your selection contain any object? > Try and get what Selection.Address actually returns, for instance: > Debug.Print Selection.address > or > Msgbox Selection.Address > > Cordially > Pascal > > "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de news: > %23Z8MP0pNKHA.3384(a)TK2MSFTNGP04.phx.gbl... >> Using a procedure which was originally created in Excel 2002 I am getting >> an error at the following line when running the same procedure on Excel >> 2007. >> >> ActiveSheet.PageSetup.PrintArea = Selection.Address >> >> I am at a loss as to why it comes out at this point and would value any >> guidance. >> >> Graham > >
From: papou on 17 Sep 2009 04:14 Graham Your code (ActiveSheet.PageSetup.PrintArea = Selection.Address) works fine for me in Excel 2007, so there should not be any need for modification at this point. Are there other sheets selected? Are there any calls to word application? (in which case the expression "Selection" could be misinterprated) Try and check with : MsgBox TypeName(Selection) = "Range" If it returns True then it means the selection is correct. One alternative could be to fully qualify objects, ie: Dim Ws As Worksheet Set Ws = ActiveWorkbook.Worksheets("Sheet1") Ws.PageSetup.PrintArea = Selection.Address Another point in MHO is that you are using too many selections in your code. Selection of cells should not be necessary, and you should consider avoiding it. HTH Cordially Pascal "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de news: uFD4NHtNKHA.3192(a)TK2MSFTNGP05.phx.gbl... > Hi Pascal, > Thanks for reply. This occurs when the procedure with a de-bug > window which says 'PrintArea' of object 'PageSetup' failed. As I said it > works perfectly fine in Exccel 2002, I just wondered what modification was > required for 2007.The code it is included in is > ActiveWindow.SelectedSheets.PrintOut Copies:=1 > Cells.Select > ActiveSheet.PageSetup.PrintArea = Selection.Address > Range("A1").Select > Sheets("Commands").Select > > > Thanks for your comments. > > Graham > > papou wrote: >> Hello Graham >> >> There's obviously some hiccup with Selection.Address in your code. >> What is the error message? >> Does your selection contain any object? >> Try and get what Selection.Address actually returns, for instance: >> Debug.Print Selection.address >> or >> Msgbox Selection.Address >> >> Cordially >> Pascal >> >> "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de >> news: %23Z8MP0pNKHA.3384(a)TK2MSFTNGP04.phx.gbl... >>> Using a procedure which was originally created in Excel 2002 I am >>> getting an error at the following line when running the same procedure >>> on Excel 2007. >>> >>> ActiveSheet.PageSetup.PrintArea = Selection.Address >>> >>> I am at a loss as to why it comes out at this point and would value any >>> guidance. >>> >>> Graham >>
From: papou on 17 Sep 2009 04:40
Ok got it. Just pasted the whole code and tested, I have the same error. Once you have selected the whole cells in your sheet (Cells.Select), the Selection.Address returns "$1:$1048576" This is what causes the error. If your goal is actually to print all sheets of your workbook then I would recommend you define the print area for each sheet. Something like for instance: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.PageSetup.PrintArea = ws.UsedRange.Address ws.PrintOut copies:=1 Next ws HTH Cordially Pascal "papou" <cpapounospamthanx(a)lapostenospamthanks.net> a �crit dans le message de news: ONrQk72NKHA.3192(a)TK2MSFTNGP05.phx.gbl... > Graham > Your code (ActiveSheet.PageSetup.PrintArea = Selection.Address) works fine > for me in Excel 2007, so there should not be any need for modification at > this point. > Are there other sheets selected? > Are there any calls to word application? (in which case the expression > "Selection" could be misinterprated) > Try and check with : > MsgBox TypeName(Selection) = "Range" > If it returns True then it means the selection is correct. > One alternative could be to fully qualify objects, ie: > Dim Ws As Worksheet > Set Ws = ActiveWorkbook.Worksheets("Sheet1") > Ws.PageSetup.PrintArea = Selection.Address > > Another point in MHO is that you are using too many selections in your > code. > Selection of cells should not be necessary, and you should consider > avoiding it. > > HTH > Cordially > Pascal > > "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de > news: uFD4NHtNKHA.3192(a)TK2MSFTNGP05.phx.gbl... >> Hi Pascal, >> Thanks for reply. This occurs when the procedure with a de-bug >> window which says 'PrintArea' of object 'PageSetup' failed. As I said it >> works perfectly fine in Exccel 2002, I just wondered what modification >> was required for 2007.The code it is included in is >> ActiveWindow.SelectedSheets.PrintOut Copies:=1 >> Cells.Select >> ActiveSheet.PageSetup.PrintArea = Selection.Address >> Range("A1").Select >> Sheets("Commands").Select >> >> >> Thanks for your comments. >> >> Graham >> >> papou wrote: >>> Hello Graham >>> >>> There's obviously some hiccup with Selection.Address in your code. >>> What is the error message? >>> Does your selection contain any object? >>> Try and get what Selection.Address actually returns, for instance: >>> Debug.Print Selection.address >>> or >>> Msgbox Selection.Address >>> >>> Cordially >>> Pascal >>> >>> "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de >>> news: %23Z8MP0pNKHA.3384(a)TK2MSFTNGP04.phx.gbl... >>>> Using a procedure which was originally created in Excel 2002 I am >>>> getting an error at the following line when running the same procedure >>>> on Excel 2007. >>>> >>>> ActiveSheet.PageSetup.PrintArea = Selection.Address >>>> >>>> I am at a loss as to why it comes out at this point and would value any >>>> guidance. >>>> >>>> Graham >>> > |