Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
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 Befo=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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
Jamey,
This works for me: With xlWs .HPageBreaks.Add .Range("A13") End With No need to .Select NickHK "Jamey Weare" wrote in message ups.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 Befo=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
I'll give that a shot... thanks.
NickHK wrote: Jamey, This works for me: With xlWs .HPageBreaks.Add .Range("A13") End With No need to .Select NickHK "Jamey Weare" wrote in message ups.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 Befo=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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
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 Befo=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. Jamey Weare wrote: I'll give that a shot... thanks. NickHK wrote: Jamey, This works for me: With xlWs .HPageBreaks.Add .Range("A13") End With No need to .Select NickHK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
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 Befo=xlRng End If End With NickHK "Jamey Weare" wrote in message oups.com... 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 Befo=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. Jamey Weare wrote: I'll give that a shot... thanks. NickHK wrote: Jamey, This works for me: With xlWs .HPageBreaks.Add .Range("A13") End With No need to .Select NickHK |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
Jamey Weare wrote:
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. -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed
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 NickHK wrote: 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 Befo=xlRng End If End With NickHK "Jamey Weare" wrote in message oups.com... 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 Befo=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. Jamey Weare wrote: I'll give that a shot... thanks. NickHK wrote: Jamey, This works for me: With xlWs .HPageBreaks.Add .Range("A13") End With No need to .Select NickHK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charts.Add error '1004' Method 'Add' of object 'Sheets' failed | Charts and Charting in Excel | |||
Intermittent Error - Method 'Add' of object 'HPageBreaks' failed | Excel Worksheet Functions | |||
Method 'Union' of object '_Global' failed error | Excel Programming | |||
HELP - 'Add' of object 'CommandBarControls' failed | Excel Discussion (Misc queries) | |||
Method 'Add' of object 'CommandBarControls' failed | Excel Discussion (Misc queries) |