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.
--
btm

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

"btm" wrote in message
...
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.
--
btm

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

"btm" wrote in message
...
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.
--
btm

See one more response at your previous thread.

btm wrote:

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.
--
btm

--

Dave Peterson