Re: 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" <GoogleGroups@xxxxxxxxxxxx>
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?
.



Relevant Pages

  • Re: late binding excel
    ... Here is some sample code using full object references for all EXCEL objects. ... Dim xlApp As Object, xlWB As Object, xlWS As Object, xlRange As Object ... ' put your code here to put the data into the cells ... The lack of an object reference when running code in EXCEL will cause ...
    (microsoft.public.access.devtoolkits)
  • Re: Find reference to specific range in code
    ... Not a problem if you use an object reference to refer to the range... ... (Excel Add-ins / Excel Programming) ... Is there any fast way to check if a specific worksheet range is ...
    (microsoft.public.excel.programming)
  • Re: launch Excel from WinForm app
    ... You have got to set literally every object reference ... The way I'm doing it now works, but when the user closes Excel, it is ... Dim pathname As String = ListBox1.SelectedItem.ToString ... Dim excelApp As Excel.Application ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Having a problem getting Excel to work from my .Net VB Program
    ... have a web based report that I need to allow the user to export to Excel. ... The function GetXLApp is below: ... Object reference not set to an instance of an ...
    (microsoft.public.excel.programming)
  • Re: VBA From MS Access: Inserting Page Break?
    ... The most commonly used ones are Activesheet, Activecell etc. ... I think the With..End With work just fine and so do the Excel constants. ... Use an object reference for every object you refer to in Excel. ... .HPageBreaks seems tb read-only. ...
    (microsoft.public.excel.programming)