using Project VBA to create a horizontal page break in Excel

[TomL]

I am exporting some data to Excel, and need to create a page break at the end
of each iteration through the loop. I have tried both of these statements to
no avail. Does anybody have any suggestions?

Thanks

We are using Project Server 2003 SP2a, Excel 2003.

Dim xlR As Excel.Range

xlR.ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

xlR.PageBreakSet

 

[Rod Gill]

Try recording a macro in Excel. Copy the recorded code to Project VBA.
You'll need to qualify the code. So if you have an xlApp variable pointing
to the Excel Application, add xlApp. in front of the Excel recorded code.

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[John]

I am exporting some data to Excel, and need to create a page break at the end
of each iteration through the loop. I have tried both of these statements to
no avail. Does anybody have any suggestions?

Thanks

We are using Project Server 2003 SP2a, Excel 2003.

Dim xlR As Excel.Range

xlR.ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

xlR.PageBreakSet

TomL,
A few years ago I wrote a macro for a client that required the use of
page breaks in Excel. I found the HPageBreaks Object to be inconsistent
in its application although I don't remember the details. I ended up
counting the total heights of rows (for standard landscape 8.5 x 11
paper) and then setting a page break before a specific row. In my case,
this is the code structure that worked:

Dim Xl As Object, s As Object
[other lines of code]
Set s = Xl.Workbooks(BookNam).Worksheets(sheetTitle)
[other lines of code]
If s.UsedRange.Height > 797 Then s.HPageBreaks.Add before:=s.Rows(8)

I'm not sure this helps, but I've been there and done that.

John
Project MVP

 

[John]

I am exporting some data to Excel, and need to create a page break at the end
of each iteration through the loop. I have tried both of these statements to
no avail. Does anybody have any suggestions?

Thanks

We are using Project Server 2003 SP2a, Excel 2003.

Dim xlR As Excel.Range

xlR.ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

xlR.PageBreakSet

Tom,
Please excuse my somewhat strange earlier reply - too much enthusiasm in
wanting to help and not enough memory of what the heck I did. Just to
confirm, I did go back and run a test of my code and it does put the
page break where needed, so at least some form of the HPageBreak does
work.

In looking at your code lines I see a couple of issues. First you have
defined xlR as an Excel range yet it looks like it is used improperly in
your first line of code. That is, you are attempting to qualify a range
with an active window. Although I didn't try it, I believe it would work
if written as follows:

Dim xl As Excel
xl.ActiveWIndow.SelectedSheets.HpageBreaks.Add Before:=ActiveCell

In your second line of code, I can find no property called
"PageBreakSet" in the Excel Object library. However, the following code
should work, assuming the intent is to set a page break before row 25:

xl.Range("25:25").PageBreak = xlPageBreakManual

Hope this helps (this time).
John

 

[TomL]

Thanks to both of you. It was very helpful.

Rod, I used your code snippet, which I had already found in your book.
owever, I had to chang the line slightly.

xlApp.ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=xlApp.ActiveCell

The xlApp in front of the ActiveCell was the part that kept failing.
ActiveCell otherwise would point somewhere else.

Thanks again.