Move page breaks programmatically...

[Theresa Smallwood]

I am trying to move page breaks in an Excel spreadsheet programmatically in
..NET, but I keep getting an error.

I am changing page breaks because there is some "heading" data on some rows
that should be kept with it's data, and if the page break occurs right after
the heading, I want to move it before the heading.

My code to change the page break's location is:

_pb.Location = (Excel.Range)_wksht.Cells[int.Parse(_rng.Row.ToString()),
"A"];

_rng.Row = 45
_rng is an Excel.Range object
_pb is one of the HPageBreaks in the spreadsheet

Error reported is:
Error code is -2146827284
Exception from HRESULT: 0x800A03EC.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.HPageBreak.set_Location(Range )
at STLTrust.Flash.Reports.PrintSpreadsheets(DateTime ReportDate) in
c:\projects\stltrust flash\reports.cs:line 679

Which, obviously, tells me nothing except that I got a COM error when
setting the location.

I have tried using a hard coded row, instead of the one from the _rng
object, since I thought maybe the problem was it wasn't getting the row from
the Range object, but I still got the error. (i.e. _pb.Location =
(Excel.Range)_wksht.Cells[45, "A"]

If someone could PLEASE tell me what I am doing wrong here, I would really
appreciate it! Thanks!

Theresa Smallwood

 

[STEVE BELL]

A long time ago I approached this same issue with a different approach.

1. Figured out the maximum number of rows per printed page.
2. Removed all page breaks (if they were hard coded to the sheet)
3. Loop down the rows with step = #1 (above)
tested that row for header
if no header, backed up a row at a time until a header was found
4. Hard coded a page break
5. Continue the loop until the end.

 

[Theresa Smallwood]

Is there a way to figure out the max number of rows on a page
programmatically, or do I have to do that manually and "hard-code" that
value? And hope that no one changes the page size, or orientation, etc.?

And I would still like to know why my code is giving me an error. All the
examples I saw said I should be able to do this, and I want to know why I
can't.

Theresa

A long time ago I approached this same issue with a different approach.

1. Figured out the maximum number of rows per printed page.
2. Removed all page breaks (if they were hard coded to the sheet)
3. Loop down the rows with step = #1 (above)
tested that row for header
if no header, backed up a row at a time until a header was found
4. Hard coded a page break
5. Continue the loop until the end.

--
steveB

Remove "AYN" from email to respond

I am trying to move page breaks in an Excel spreadsheet programmatically
in .NET, but I keep getting an error.

I am changing page breaks because there is some "heading" data on some
rows that should be kept with it's data, and if the page break occurs
right after the heading, I want to move it before the heading.

My code to change the page break's location is:

_pb.Location = (Excel.Range)_wksht.Cells[int.Parse(_rng.Row.ToString()),
"A"];

_rng.Row = 45
_rng is an Excel.Range object
_pb is one of the HPageBreaks in the spreadsheet

Error reported is:
Error code is -2146827284
Exception from HRESULT: 0x800A03EC.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.HPageBreak.set_Location(Range )
at STLTrust.Flash.Reports.PrintSpreadsheets(DateTime ReportDate) in
c:\projects\stltrust flash\reports.cs:line 679

Which, obviously, tells me nothing except that I got a COM error when
setting the location.

I have tried using a hard coded row, instead of the one from the _rng
object, since I thought maybe the problem was it wasn't getting the row
from the Range object, but I still got the error. (i.e. _pb.Location =
(Excel.Range)_wksht.Cells[45, "A"]

If someone could PLEASE tell me what I am doing wrong here, I would
really appreciate it! Thanks!

Theresa Smallwood

 

[Tom Ogilvy]

If you use only automatic pagebreaks, then you can determine the number of
rows on a page by seeing where the automatic pagebreaks are located. Of
course the problem gets more complex if all the rows are not the same
height.

--
Regards,
Tom Ogilvy

Is there a way to figure out the max number of rows on a page
programmatically, or do I have to do that manually and "hard-code" that
value? And hope that no one changes the page size, or orientation, etc.?

And I would still like to know why my code is giving me an error. All the
examples I saw said I should be able to do this, and I want to know why I
can't.

Theresa

A long time ago I approached this same issue with a different approach.

1. Figured out the maximum number of rows per printed page.
2. Removed all page breaks (if they were hard coded to the sheet)
3. Loop down the rows with step = #1 (above)
tested that row for header
if no header, backed up a row at a time until a header was found
4. Hard coded a page break
5. Continue the loop until the end.

--
steveB

Remove "AYN" from email to respond

I am trying to move page breaks in an Excel spreadsheet programmatically
in .NET, but I keep getting an error.

I am changing page breaks because there is some "heading" data on some
rows that should be kept with it's data, and if the page break occurs
right after the heading, I want to move it before the heading.

My code to change the page break's location is:

_pb.Location = (Excel.Range)_wksht.Cells[int.Parse(_rng.Row.ToString()),
"A"];

_rng.Row = 45
_rng is an Excel.Range object
_pb is one of the HPageBreaks in the spreadsheet

Error reported is:
Error code is -2146827284
Exception from HRESULT: 0x800A03EC.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.HPageBreak.set_Location(Range )
at STLTrust.Flash.Reports.PrintSpreadsheets(DateTime ReportDate) in
c:\projects\stltrust flash\reports.cs:line 679

Which, obviously, tells me nothing except that I got a COM error when
setting the location.

I have tried using a hard coded row, instead of the one from the _rng
object, since I thought maybe the problem was it wasn't getting the row
from the Range object, but I still got the error. (i.e. _pb.Location =
(Excel.Range)_wksht.Cells[45, "A"]

If someone could PLEASE tell me what I am doing wrong here, I would
really appreciate it! Thanks!

Theresa Smallwood

 

[Theresa Smallwood]

Ok, I am trying this approach, and it puts the pagebreaks I want in, but I'm
having an issue with one thing - how do I remove the automatic pagebreaks?
When I try to loop through and Delete() them, I get a COM exception (similar
to the one I got with moving the page breaks.)

Theresa

A long time ago I approached this same issue with a different approach.

1. Figured out the maximum number of rows per printed page.
2. Removed all page breaks (if they were hard coded to the sheet)
3. Loop down the rows with step = #1 (above)
tested that row for header
if no header, backed up a row at a time until a header was found
4. Hard coded a page break
5. Continue the loop until the end.

--
steveB

Remove "AYN" from email to respond

I am trying to move page breaks in an Excel spreadsheet programmatically
in .NET, but I keep getting an error.

I am changing page breaks because there is some "heading" data on some
rows that should be kept with it's data, and if the page break occurs
right after the heading, I want to move it before the heading.

My code to change the page break's location is:

_pb.Location = (Excel.Range)_wksht.Cells[int.Parse(_rng.Row.ToString()),
"A"];

_rng.Row = 45
_rng is an Excel.Range object
_pb is one of the HPageBreaks in the spreadsheet

Error reported is:
Error code is -2146827284
Exception from HRESULT: 0x800A03EC.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.HPageBreak.set_Location(Range )
at STLTrust.Flash.Reports.PrintSpreadsheets(DateTime ReportDate) in
c:\projects\stltrust flash\reports.cs:line 679

Which, obviously, tells me nothing except that I got a COM error when
setting the location.

I have tried using a hard coded row, instead of the one from the _rng
object, since I thought maybe the problem was it wasn't getting the row
from the Range object, but I still got the error. (i.e. _pb.Location =
(Excel.Range)_wksht.Cells[45, "A"]

If someone could PLEASE tell me what I am doing wrong here, I would
really appreciate it! Thanks!

Theresa Smallwood

 

[STEVE BELL]

Theresa,

Usually when you force a page break, the auto page breaks shift from the
inserted row to the
end of the document. You don't need to delete auto page breaks, just the
inserted ones you may have put in previously. (sorry if I mislead you)

In short - it shouldn't be an issue.

--
steveB

Remove "AYN" from email to respond

Ok, I am trying this approach, and it puts the pagebreaks I want in, but
I'm having an issue with one thing - how do I remove the automatic
pagebreaks?
When I try to loop through and Delete() them, I get a COM exception
(similar to the one I got with moving the page breaks.)

Theresa

A long time ago I approached this same issue with a different approach.

1. Figured out the maximum number of rows per printed page.
2. Removed all page breaks (if they were hard coded to the sheet)
3. Loop down the rows with step = #1 (above)
tested that row for header
if no header, backed up a row at a time until a header was found
4. Hard coded a page break
5. Continue the loop until the end.

--
steveB

Remove "AYN" from email to respond

I am trying to move page breaks in an Excel spreadsheet programmatically
in .NET, but I keep getting an error.

I am changing page breaks because there is some "heading" data on some
rows that should be kept with it's data, and if the page break occurs
right after the heading, I want to move it before the heading.

My code to change the page break's location is:

_pb.Location = (Excel.Range)_wksht.Cells[int.Parse(_rng.Row.ToString()),
"A"];

_rng.Row = 45
_rng is an Excel.Range object
_pb is one of the HPageBreaks in the spreadsheet

Error reported is:
Error code is -2146827284
Exception from HRESULT: 0x800A03EC.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.HPageBreak.set_Location(Range )
at STLTrust.Flash.Reports.PrintSpreadsheets(DateTime ReportDate) in
c:\projects\stltrust flash\reports.cs:line 679

Which, obviously, tells me nothing except that I got a COM error when
setting the location.

I have tried using a hard coded row, instead of the one from the _rng
object, since I thought maybe the problem was it wasn't getting the row
from the Range object, but I still got the error. (i.e. _pb.Location =
(Excel.Range)_wksht.Cells[45, "A"]

If someone could PLEASE tell me what I am doing wrong here, I would
really appreciate it! Thanks!

Theresa Smallwood

 

[Tom Ogilvy]

To elaborate. If there is an automatic pagebreak, it is because the
parameters of your printer and the layout of the sheet dictate that a
pagebreak must be there. You eliminate an automatic pagebreak by putting a
manual pagebreak before the automatic pagebreak.

--
Regards,
Tom Ogilvy

Ok, I am trying this approach, and it puts the pagebreaks I want in, but I'm
having an issue with one thing - how do I remove the automatic pagebreaks?
When I try to loop through and Delete() them, I get a COM exception (similar
to the one I got with moving the page breaks.)

Theresa

A long time ago I approached this same issue with a different approach.

1. Figured out the maximum number of rows per printed page.
2. Removed all page breaks (if they were hard coded to the sheet)
3. Loop down the rows with step = #1 (above)
tested that row for header
if no header, backed up a row at a time until a header was found
4. Hard coded a page break
5. Continue the loop until the end.

--
steveB

Remove "AYN" from email to respond

I am trying to move page breaks in an Excel spreadsheet programmatically
in .NET, but I keep getting an error.

I am changing page breaks because there is some "heading" data on some
rows that should be kept with it's data, and if the page break occurs
right after the heading, I want to move it before the heading.

My code to change the page break's location is:

_pb.Location = (Excel.Range)_wksht.Cells[int.Parse(_rng.Row.ToString()),
"A"];

_rng.Row = 45
_rng is an Excel.Range object
_pb is one of the HPageBreaks in the spreadsheet

Error reported is:
Error code is -2146827284
Exception from HRESULT: 0x800A03EC.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.HPageBreak.set_Location(Range )
at STLTrust.Flash.Reports.PrintSpreadsheets(DateTime ReportDate) in
c:\projects\stltrust flash\reports.cs:line 679

Which, obviously, tells me nothing except that I got a COM error when
setting the location.

I have tried using a hard coded row, instead of the one from the _rng
object, since I thought maybe the problem was it wasn't getting the row
from the Range object, but I still got the error. (i.e. _pb.Location =
(Excel.Range)_wksht.Cells[45, "A"]

If someone could PLEASE tell me what I am doing wrong here, I would
really appreciate it! Thanks!

Theresa Smallwood