Intermittent Error - Method 'Add' of object 'HPageBreaks' failed

[Jamey Weare]

I am having trouble with an automation project that I am working on. I
am generating a report in a spreadsheet. Originally I attempted to do
it in Access, but it did not provide me enough flexibility to
accomplish what I was looking to do. The report is an old report that
was manually created in Excel and they wanted to format to remain the
same, but automate it. Everything has been going great up until this
one bit of code...

Essentially the report is in a format something like this:
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Type 1
| header 1 | header 2 | header 3 |
| details | details | details |
----------------------------------------------
subtotals

Type 2
| header 1 | header 2 | header 3 |
| details | details | details |
----------------------------------------------
subtotals

| header 1 | header 2 | header 3 |
grand totals

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
For most of my organizational units this all fits on one page, but for
a few it does not. When it does not I want to drop the Type 2 section
to its own page. The code below is what I am using to accomplish this
and it is working most of the time. I am randomly getting the error
that is listed in the subject line of this message.

If BotRow > 13 Then
BotRow = BotRow + 3
Set xlRng = xlWs.Range("A" & BotRow)
xlRng.Select
xlWs.HPageBreaks.Add Before:=ActiveCell <<<< Error on this
line
End If

Any ideas as to what could be causing this error? It does not seem to
matter if Excel is already open or if all instances are closed.

Is there another way to insert a page break at a given point?

I am using Access 2002 SP-1 and Excel 2003 SP-3 on a WinXP machine. If
you need more info to come up with an answer just let me know.


Jamey Weare
Business Analyst
Florida Power & Light

 

[Jamey Weare]

I am not sure what the answer to your question is. I am really curious
as to why you answered to my topic and changed the subject. It took me
forever to find it again.

 

[NickHK]

Jamey,
This works for me:
With xlWs
.HPageBreaks.Add .Range("A13")
End With

No need to .Select

NickHK

 

[Jamey Weare]

I'll give that a shot... thanks.

Jamey,
This works for me:
With xlWs
.HPageBreaks.Add .Range("A13")
End With

No need to .Select

NickHK

 

[Jamey Weare]

I get a compile error that selects the Add method, stating that an
argument is not optional. I have Access 2002 which I guess has a
different version of that method than you.
Anyone have any other ideas?

The code I have at the moment is:

BotRow = xlWs.Cells.SpecialCells(xlCellTypeLastCell).Row

Set xlRng = xlWs.Cells(BotRow + 3, 1)
xlRng.Select

If BotRow > 13 Then
xlWs.HPageBreaks.Add Before:=ActiveCell
End If


I have tried quite a few different ways of doing this, bt it still
fails intermitently. It always runs fine the first time through.

I get an error that either states that the Add method failed as stated
in the subject or I get a Run-Time error '1004': Application-defined or
object defined error. If I close the database and then run the code
again it works just fine.

 

[NickHK]

You are writing this code in Access ?
If so, you can't use ActiveCell (an other unqualified Excel objects) and
..Selects are a bad idea.

So use
With xlWs
BotRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set xlRng = .Cells(BotRow + 3, 1)

If BotRow > 13 Then
.HPageBreaks.Add Before:=xlRng
End If
End With

NickHK

 

[Chris O'C via OfficeKB.com]

I am using Access 2002 SP-1 and Excel 2003 SP-3 on a WinXP machine.

Double check this. Office 2002 (XP) had three service packs, but Office 2003
has only had two service packs so far. Did you transpose these numbers when
you typed them or does the Excel version have a typo? Access 2002 should
have either SP-2 or SP-3 installed, and Excel 2003 should have SP-2 installed.
If your computer doesn't have the latest service packs, these programs may
not work correctly, hence "intermittent" errors.

 

[Jamey Weare]

That got it... thanks so much. I did suspect that the ActiveCell
reference was the culprit, but couldn't find any other code that used a
different opject there and wasn't sure how to fix it.

Thanks again!

~Jamey