need help with hpagebreak

[Guest]

I'm trying to create horizontal page breaks programmatically in a
spreadsheet, but I can't get it to work.

The program runs without error, but it appears as though the Excel margin
settings override my hpagebreak settings.

Below is a section of code out of my program. I'm trying to create a
pagebreak every 35 lines.

hpbCnt = 0
hpbRow = 0
Excelsheet.PageSetup.PrintArea = "$A$1:$D$" & CStr(rownum)
Dim cell As String
Do While hpbCnt < rownum + 10
hpbRow = hpbRow + 35
hpbCnt = hpbCnt + 1
cell = "d" & CStr(hpbRow)
Worksheets(1).HPageBreaks(hpbCnt).Location = Worksheets(1).Range("d" &
CStr(hpbRow))
Loop

The actual program pulls data from an AutoCAD drawing and inserts it into
Excel. The data in Excel is then formatted to be printed onto label sheets. I
need the page breaks in the correct area of the spreadsheet to get the text
positioned properly on the labels.

I would appreciate any suggestions for using hpagebreak, or any other means
of correcting this problem.

 

[Robin Hammond]

btm,

Something like this might do it.

Option Explicit
Option Private Module

Public Sub HPageBreaksAtInterval(shTarget As Worksheet, _
lInterval As Long, _
Optional lMaxPages As Long = 30)

Dim lPageCounter As Long

With shTarget
.Cells.PageBreak = xlPageBreakNone
For lPageCounter = 1 To lMaxPages
.HPageBreaks.Add .Cells((lPageCounter * lInterval) + 1, 1)
Next lPageCounter
End With
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[NickHK]

btm,
Did you read the advise and links in the previous thread you started
"hpagebreak and vpagebreak" ?
If so, you would see what .HPageBreaks.Count is equal to and why you should
be getting an error of "Subscript out of range".

NickHK

 

[Dave Peterson]

See one more response at your previous thread.

I'm trying to create horizontal page breaks programmatically in a
spreadsheet, but I can't get it to work.

The program runs without error, but it appears as though the Excel margin
settings override my hpagebreak settings.

Below is a section of code out of my program. I'm trying to create a
pagebreak every 35 lines.

hpbCnt = 0
hpbRow = 0
Excelsheet.PageSetup.PrintArea = "$A$1:$D$" & CStr(rownum)
Dim cell As String
Do While hpbCnt < rownum + 10
hpbRow = hpbRow + 35
hpbCnt = hpbCnt + 1
cell = "d" & CStr(hpbRow)
Worksheets(1).HPageBreaks(hpbCnt).Location = Worksheets(1).Range("d" &
CStr(hpbRow))
Loop

The actual program pulls data from an AutoCAD drawing and inserts it into
Excel. The data in Excel is then formatted to be printed onto label sheets. I
need the page breaks in the correct area of the spreadsheet to get the text
positioned properly on the labels.

I would appreciate any suggestions for using hpagebreak, or any other means
of correcting this problem.