Inserting Page Break Into Excel Worksheet From MS Access VBA?

[(PeteCresswell)]

I posted this in Microsoft.Public.Excel.Programming early today,
but haven't gotten any nibbles yet.

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

 

[(PeteCresswell)]

Per (PeteCresswell):

I posted this in Microsoft.Public.Excel.Programming early today,
but haven't gotten any nibbles yet.

I think I got an answer .Excel.Programming:
------------------------------------------------------
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