[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

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

Jamey Weare

12/19/2006 7:34:00 PM

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
2 Answers

Jamey Weare

12/19/2006 9:17:00 PM

0

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.

On Dec 19, 2:46 pm, Cheryl <Che...@discussions.microsoft.com> wrote:
> I suspect that the answer is no, but just in case some genius has a
> workaround, I'll ask anyway. Is there a way to hide or delete the rows of a
> pivot table where the sum of <whatever is chosen as the data field> equals
> zero for a field? For instance, the "reference" column of a large database
> has several rows for which the total dollar value for Ref # 00800400 is zero.
> I don't want that reference's information included in my displayed pivot
> table summary, but I don't want to go through the whole database before
> creating my pivot table to eliminate all rows for a reference if the total =
> zero.

NickHK

12/20/2006 4:28:00 AM

0

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

No need to .Select

NickHK

"Jamey Weare" <jamey_weare@fpl.com> wrote in message
news:1166556838.253442.49040@t46g2000cwa.googlegroups.com...
> 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
>