PageBreak problem. How to improve performance?

[liu_jz]

Dear,

I have a program that makes a report and add some pagebreaks. The
following code processed 21 records, and added 18 pagebreaks.

dim xlWorksheet As Excel.Worksheet
Dim intCounter As Integer
Dim intBreakGroupCounter As Integer

.......

With xlWorksheet
For i = 0 To lngRecordCnt - 1

......

If gintPageBreak > 0 Then
If intCounter > intBreakGroupCounter And (intCounter Mod
gintPageBreak) = 0 Then
.HPageBreaks.Add (.Cells(intCounter), 1)) <----
intBreakGroupCounter = intCounter
End If
End If
Next i
End With

1. When I set the default printer to the printer that was not ready,
the program took 28 seconds.
2. When I set the default printer to the printer that was ready, such
as Microsoft Office Document Image Writer, the program took 12 seconds.
3. When I deleted or remarked the pagebreak code line, it took 1
second.
' .HPageBreaks.Add (.Cells(intCounter), 1))

How can I improve the performance when I add some pagebreaks.


Liu Jianzhong

 

[Jim Cone]

Liu,
1. You have an extra ")" after .Cells....
2. Turning off the display of pagebreaks will often speed things up.

Try this out...
'----------------
If gintPageBreak > 0 Then
If (intCounter > intBreakGroupCounter) And _
(intCounter Mod gintPageBreak = 0) Then
.DisplayPageBreaks = False
.HPageBreaks.Add (.Cells(intCounter, 1)) '<----
intBreakGroupCounter = intCounter
End If
End If
'-------------------
Regards,
Jim Cone
San Francisco, USA


<[email protected]>
wrote in message
Dear,
I have a program that makes a report and add some pagebreaks. The
following code processed 21 records, and added 18 pagebreaks.
dim xlWorksheet As Excel.Worksheet
Dim intCounter As Integer
Dim intBreakGroupCounter As Integer......

With xlWorksheet
For i = 0 To lngRecordCnt - 1
......
If gintPageBreak > 0 Then
If intCounter > intBreakGroupCounter And (intCounter Mod
gintPageBreak) = 0 Then
.HPageBreaks.Add (.Cells(intCounter), 1)) <----
intBreakGroupCounter = intCounter
End If
End If
Next i
End With

1. When I set the default printer to the printer that was not ready,
the program took 28 seconds.
2. When I set the default printer to the printer that was ready, such
as Microsoft Office Document Image Writer, the program took 12 seconds.
3. When I deleted or remarked the pagebreak code line, it took 1
second.
' .HPageBreaks.Add (.Cells(intCounter), 1))
How can I improve the performance when I add some pagebreaks.
Liu Jianzhong

 

[Guest]

Jim Cone,

I have set the excel application to be invisible. And by debugging, I found
xlWorksheet.DisplayPageBreaks is Faulse.

Set gxlApp = CreateObject("Excel.Application")
gxlApp.Visible= False

Best regards!
Liu Jianzhong