Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
In Excel VBA, seems like all I have to do is select a row and invoke
..HPageBreaks.Add. viz: ----------------------------------------------------------- Rows("36:36").Select ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ----------------------------------------------------------- But from MS Access VBA, .HPageBreaks seems tb read-only. e.g. ----------------------------------------------------------- 3300 With theSS.Worksheets(theWorkSheetName) 3301 .Rows(myLastTargetRow + 1).Select 3302 .HPageBreaks.Add befo=.ActiveCell 3309 End With ----------------------------------------------------------- traps out with "Line 003302 0438: Object doesn't support this property or method" And when I invoke View | Object Browser | Excel | WorkSheet | HPageBreaks, it describes it as "read-only". My first suspicion is that I'm doing something goofy with my object reference - like looking in the wrong place in the model or at the wrong model. Can anybody shed some light? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
Excel automation requires a slightly different approach.
Some guidelines ... Use an object reference for every object you refer to in Excel. (that also means do not use ActiveCell, Selection or anything similar) Do not select anything. Do not use the "With" construct. Do not use Excel constants. So you end up with something like this... (assumes SS is the Excel application object reference) Set WS = SS.Worksheets(name) Set Rng = WS.Rows("20:40") TargetRow = 11 'Row 30 Rng(TargetRow).PageBreak = -4135 'xlPageBreakManual -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "PeteCresswell" wrote in message In Excel VBA, seems like all I have to do is select a row and invoke ..HPageBreaks.Add. viz: ----------------------------------------------------------- Rows("36:36").Select ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ----------------------------------------------------------- But from MS Access VBA, .HPageBreaks seems tb read-only. e.g. ----------------------------------------------------------- 3300 With theSS.Worksheets(theWorkSheetName) 3301 .Rows(myLastTargetRow + 1).Select 3302 .HPageBreaks.Add befo=.ActiveCell 3309 End With ----------------------------------------------------------- traps out with "Line 003302 0438: Object doesn't support this property or method" And when I invoke View | Object Browser | Excel | WorkSheet | HPageBreaks, it describes it as "read-only". My first suspicion is that I'm doing something goofy with my object reference - like looking in the wrong place in the model or at the wrong model. Can anybody shed some light? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
Hi Jim,
In my view one needs to just avoid the Globals that we normally use in Excel. The most commonly used ones are Activesheet, Activecell etc. I think the With..End With work just fine and so do the Excel constants. Alok "Jim Cone" wrote: Excel automation requires a slightly different approach. Some guidelines ... Use an object reference for every object you refer to in Excel. (that also means do not use ActiveCell, Selection or anything similar) Do not select anything. Do not use the "With" construct. Do not use Excel constants. So you end up with something like this... (assumes SS is the Excel application object reference) Set WS = SS.Worksheets(name) Set Rng = WS.Rows("20:40") TargetRow = 11 'Row 30 Rng(TargetRow).PageBreak = -4135 'xlPageBreakManual -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "PeteCresswell" wrote in message In Excel VBA, seems like all I have to do is select a row and invoke ..HPageBreaks.Add. viz: ----------------------------------------------------------- Rows("36:36").Select ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell ----------------------------------------------------------- But from MS Access VBA, .HPageBreaks seems tb read-only. e.g. ----------------------------------------------------------- 3300 With theSS.Worksheets(theWorkSheetName) 3301 .Rows(myLastTargetRow + 1).Select 3302 .HPageBreaks.Add befo=.ActiveCell 3309 End With ----------------------------------------------------------- traps out with "Line 003302 0438: Object doesn't support this property or method" And when I invoke View | Object Browser | Excel | WorkSheet | HPageBreaks, it describes it as "read-only". My first suspicion is that I'm doing something goofy with my object reference - like looking in the wrong place in the model or at the wrong model. Can anybody shed some light? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
There seems to be an exception for almost everything.<g
Jim Cone San Francisco, USA 178510 - PRB: Excel Automation Fails Second Time Code Runs http://support.microsoft.com/default...en-us%3b178510 189618 - PRB: Automation Error Calling Unqualified Method or Property http://support.microsoft.com/default...en-us%3b189618 199219 - XL2000: Automation Doesn't Release Excel Object from Memory http://support.microsoft.com/default...;en-us;q199219 319832 - INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic http://support.microsoft.com/default...en-us%3b319832 317109 - Visual Basic .Net & VisualC# OFF97: Microsoft Office 97 Automation Help File Available <http://support.microsoft.com/kb/q167223/ Communicating with Other Applications <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbawd10/html/wohowcommunication.asp Cross-Application Automation Samples <http://www.microsoft.com/officedev/articles/sampauto.htm <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno97ta/html/sampauto.asp <http://www.microsoft.com/downloads/details.aspx?familyid=a16f4288-9695-45f3-a564-18cff2bf61f5&displaylang=en http://support.microsoft.com/support...aqVBOffice.asp Frequently Asked Questions about Microsoft Office Automation Using Visual Basic http://support.microsoft.com/support...fdevinapps.asp Programming Office from Within Office http://support.microsoft.com/default...b;EN-US;244167 INFO: Writing Automation Clients for Multiple Office Versions http://support.microsoft.com/default...b;en-us;245115 INFO: Using Early Binding and Late Binding in Automation "Alok" wrote in message Hi Jim, In my view one needs to just avoid the Globals that we normally use in Excel. The most commonly used ones are Activesheet, Activecell etc. I think the With..End With work just fine and so do the Excel constants. Alok "Jim Cone" wrote: Excel automation requires a slightly different approach. Some guidelines ... Use an object reference for every object you refer to in Excel. (that also means do not use ActiveCell, Selection or anything similar) Do not select anything. Do not use the "With" construct. Do not use Excel constants. So you end up with something like this... (assumes SS is the Excel application object reference) Set WS = SS.Worksheets(name) Set Rng = WS.Rows("20:40") TargetRow = 11 'Row 30 Rng(TargetRow).PageBreak = -4135 'xlPageBreakManual -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
Jim, you are correct.
Thanks. Alok "Jim Cone" wrote: There seems to be an exception for almost everything.<g Jim Cone San Francisco, USA 178510 - PRB: Excel Automation Fails Second Time Code Runs http://support.microsoft.com/default...en-us%3b178510 189618 - PRB: Automation Error Calling Unqualified Method or Property http://support.microsoft.com/default...en-us%3b189618 199219 - XL2000: Automation Doesn't Release Excel Object from Memory http://support.microsoft.com/default...;en-us;q199219 319832 - INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic http://support.microsoft.com/default...en-us%3b319832 317109 - Visual Basic .Net & VisualC# OFF97: Microsoft Office 97 Automation Help File Available <http://support.microsoft.com/kb/q167223/ Communicating with Other Applications <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbawd10/html/wohowcommunication.asp Cross-Application Automation Samples <http://www.microsoft.com/officedev/articles/sampauto.htm <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno97ta/html/sampauto.asp <http://www.microsoft.com/downloads/details.aspx?familyid=a16f4288-9695-45f3-a564-18cff2bf61f5&displaylang=en http://support.microsoft.com/support...aqVBOffice.asp Frequently Asked Questions about Microsoft Office Automation Using Visual Basic http://support.microsoft.com/support...fdevinapps.asp Programming Office from Within Office http://support.microsoft.com/default...b;EN-US;244167 INFO: Writing Automation Clients for Multiple Office Versions http://support.microsoft.com/default...b;en-us;245115 INFO: Using Early Binding and Late Binding in Automation "Alok" wrote in message Hi Jim, In my view one needs to just avoid the Globals that we normally use in Excel. The most commonly used ones are Activesheet, Activecell etc. I think the With..End With work just fine and so do the Excel constants. Alok "Jim Cone" wrote: Excel automation requires a slightly different approach. Some guidelines ... Use an object reference for every object you refer to in Excel. (that also means do not use ActiveCell, Selection or anything similar) Do not select anything. Do not use the "With" construct. Do not use Excel constants. So you end up with something like this... (assumes SS is the Excel application object reference) Set WS = SS.Worksheets(name) Set Rng = WS.Rows("20:40") TargetRow = 11 'Row 30 Rng(TargetRow).PageBreak = -4135 'xlPageBreakManual -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
Per Jim Cone:
Use an object reference for every object you refer to in Excel. (that also means do not use ActiveCell, Selection or anything similar) Do not select anything. Do not use the "With" construct. Do not use Excel constants. So you end up with something like this... (assumes SS is the Excel application object reference) Set WS = SS.Worksheets(name) Set Rng = WS.Rows("20:40") TargetRow = 11 'Row 30 Rng(TargetRow).PageBreak = -4135 'xlPageBreakManual Thanks. I never would have figured that out in a million years. Here's what I wound up going with: ------------------------------------------------------------------------------- 3300 With theSS.Worksheets(theWorkSheetName) 3301 .Rows(myLastTargetRow + 1).PageBreak = xlPageBreakManual 3309 End With -------------------------------------------------------------------------------- What's the problem with using Excel constants? I'm using them rather extensively, so maybe my code needs some overhauling. Something about the needed library not always being present on user's machines? Ditto "With".... I live and die by it - although I avoid nesting. Is there some weakness/exposure there? MS Access 2003... -- PeteCresswell |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
"What's the problem with using Excel constants?"
The problem is you are running the program from Access and relying on Access to know the value of Excel constants. One can prepend the application.object to the constant... SS.xlCalculationManual 'value of -4135 The "With" issue is explained somewhere in that list of reference material I posted. Using "With" can (but not always) leave an orphan reference and prevent the Excel application from quitting. Task manager being the only way to close it. Object references are the way to go. Jim Cone http://www.officeletter.com/blink/specialsort.html "(PeteCresswell)" wrote in message Per Jim Cone: Use an object reference for every object you refer to in Excel. (that also means do not use ActiveCell, Selection or anything similar) Do not select anything. Do not use the "With" construct. Do not use Excel constants. So you end up with something like this... (assumes SS is the Excel application object reference) Set WS = SS.Worksheets(name) Set Rng = WS.Rows("20:40") TargetRow = 11 'Row 30 Rng(TargetRow).PageBreak = -4135 'xlPageBreakManual Thanks. I never would have figured that out in a million years. Here's what I wound up going with: ------------------------------------------------------------------------------- 3300 With theSS.Worksheets(theWorkSheetName) 3301 .Rows(myLastTargetRow + 1).PageBreak = xlPageBreakManual 3309 End With -------------------------------------------------------------------------------- What's the problem with using Excel constants? I'm using them rather extensively, so maybe my code needs some overhauling. Something about the needed library not always being present on user's machines? Ditto "With".... I live and die by it - although I avoid nesting. Is there some weakness/exposure there? MS Access 2003... -- PeteCresswell |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
If you are using Automation/early binding (ie setting a reference and Dim
XLApp as Excel.Application) using constants defined in Excel's type library should be fine. However, using Automation/late binding (Dim XLApp As Object/CreateObject), you have to use the numeric values as there no where look up the constant's value. As for With, I've not had problem, but I presume one/some of those links that Jim posted indicate potential problems. (Just read the post on With/CreateObject.) However, with automation I tend to use more object variables, so the need for With block is reduced. Heartily agree with the assumed objected (ActiveCell, Selection, Range(..), etc). Always be explicit about the object in use. As for .Select, there is usually no need to use it in any environment. Just my 2c. NickHK "(PeteCresswell)" wrote in message ... Per Jim Cone: Use an object reference for every object you refer to in Excel. (that also means do not use ActiveCell, Selection or anything similar) Do not select anything. Do not use the "With" construct. Do not use Excel constants. So you end up with something like this... (assumes SS is the Excel application object reference) Set WS = SS.Worksheets(name) Set Rng = WS.Rows("20:40") TargetRow = 11 'Row 30 Rng(TargetRow).PageBreak = -4135 'xlPageBreakManual Thanks. I never would have figured that out in a million years. Here's what I wound up going with: -------------------------------------------------------------------------- ----- 3300 With theSS.Worksheets(theWorkSheetName) 3301 .Rows(myLastTargetRow + 1).PageBreak = xlPageBreakManual 3309 End With -------------------------------------------------------------------------- ------ What's the problem with using Excel constants? I'm using them rather extensively, so maybe my code needs some overhauling. Something about the needed library not always being present on user's machines? Ditto "With".... I live and die by it - although I avoid nesting. Is there some weakness/exposure there? MS Access 2003... -- PeteCresswell |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA From MS Access: Inserting Page Break?
Per Jim Cone:
"What's the problem with using Excel constants?" The problem is you are running the program from Access and relying on Access to know the value of Excel constants. One can prepend the application.object to the constant... SS.xlCalculationManual 'value of -4135 The "With" issue is explained somewhere in that list of reference material I posted. Using "With" can (but not always) leave an orphan reference and prevent the Excel application from quitting. Task manager being the only way to close it. Object references are the way to go. Thanks. Also, thanks for the observation on .Select. Looked at my code and saw a good bit of it.... and now it's pretty obvious to me that it's a Bad Idea. Been rooting them out one-by-one and replacing them with direct object refs. -- PeteCresswell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INSERTING ROWS IMMEDIATELY AFTER PAGE BREAK | Excel Discussion (Misc queries) | |||
Inserting page break | Excel Discussion (Misc queries) | |||
Inserting A row & page break | Excel Programming | |||
Inserting header @ page break | Excel Discussion (Misc queries) | |||
Inserting rows before page break | Excel Programming |