VBA From MS Access: Inserting Page Break?

[PeteCresswell]

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 Before:=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 before:=.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?

 

[Jim Cone]

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" <[email protected]>
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 Before:=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 before:=.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?

 

[Alok]

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]

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.aspx?scid=kb;en-us;178510

189618 - PRB: Automation Error Calling Unqualified Method or Property

http://support.microsoft.com/default.aspx?scid=kb;en-us;189618

199219 - XL2000: Automation Doesn't Release Excel Object from Memory

http://support.microsoft.com/default.aspx?scid=KB;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.aspx?scid=kb;en-us;319832

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/...88-9695-45f3-a564-18cff2bf61f5&displaylang=en>

http://support.microsoft.com/support/OfficeDev/FaqVBOffice.asp
Frequently Asked Questions about Microsoft Office Automation Using Visual Basic

http://support.microsoft.com/support/OfficeDev/offdevinapps.asp
Programming Office from Within Office

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation



"Alok" <[email protected]>
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

 

[Alok]

Jim, you are correct.
Thanks.
Alok

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.aspx?scid=kb;en-us;178510

189618 - PRB: Automation Error Calling Unqualified Method or Property

http://support.microsoft.com/default.aspx?scid=kb;en-us;189618

199219 - XL2000: Automation Doesn't Release Excel Object from Memory

http://support.microsoft.com/default.aspx?scid=KB;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.aspx?scid=kb;en-us;319832

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/...88-9695-45f3-a564-18cff2bf61f5&displaylang=en>

http://support.microsoft.com/support/OfficeDev/FaqVBOffice.asp
Frequently Asked Questions about Microsoft Office Automation Using Visual Basic

http://support.microsoft.com/support/OfficeDev/offdevinapps.asp
Programming Office from Within Office

http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation



"Alok" <[email protected]>
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

 

[(PeteCresswell)]

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...

 

[Jim Cone]

Re: "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)" <[email protected]>
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...

 

[NickHK]

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)]

Per Jim Cone:

Re: "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.