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

J

Jamey Weare

I have tried to accomplish this a number of ways, but the code 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.


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

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

If BotRow > 13 Then
xlWs.HPageBreaks.Add Before:=ActiveCell <---- Error occurs on
this line.
End If

Someone told me in another board that I could skip making the selection
and then do

xlws.HPageBreaks.Add.Range ("A13")

With that I get a compile error. They must be using a different version
of Excel. This code is being run from an Access automation application
where I am filling in the cells from data that was shaped in access
queries. I have Access 2002 SP1 and Excel 2003 SP2.

I am at my wits end with this error so any help would be appreciated.
 
J

Jim Cone

Access is not going to know what "ActiveCell" means.
Also, there should be a space between Add and the Range, so try ...
xlWs.HPageBreaks.Add xlWs.Range("A13")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Jamey Weare"
<[email protected]>
wrote in message
I have tried to accomplish this a number of ways, but the code 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.

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

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

If BotRow > 13 Then
xlWs.HPageBreaks.Add Before:=ActiveCell <---- Error occurs on
this line.
End If

Someone told me in another board that I could skip making the selection
and then do

xlws.HPageBreaks.Add.Range ("A13")

With that I get a compile error. They must be using a different version
of Excel. This code is being run from an Access automation application
where I am filling in the cells from data that was shaped in access
queries. I have Access 2002 SP1 and Excel 2003 SP2.
I am at my wits end with this error so any help would be appreciated.
 
Top