Setting Header and footer
Posted by Nishith Desai on July 03, 2001 6:43 AM
I had recorded a macro for setting header and footer,
Now if I keep my HP 670c deskjet as default printer the macros runs very very slow and if i keep EPSON FX 800 as my default printer then macros runs very fast.
Now what is the problem please help me:
the macro has following code:
Attribute VB_Name = "Module12"
Sub clearhf()
Attribute clearhf.VB_Description = "Macro recorded 5/25/2001 by Nishith Desai"
Attribute clearhf.VB_ProcData.VB_Invoke_Func = " \n14"
'
'
Dim i As Integer
Dim c As Integer
Dim strleft As String
Dim strcentertop As String
Dim strcenterbottom As String
Dim strrighttop As String
Application.ScreenUpdating = False
strleft = Worksheets("design").Range("b2").Value
strcentertop = Worksheets("design").Range("e2").Value
strcenterbottom = Worksheets("design").Range("e3").Value
Application.ScreenUpdating = False
'i = Sheets.Count
For i = 1 To 4
c = 1 + c
With Worksheets(i).PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
' End With
' ActiveSheet.PageSetup.PrintArea = ""
' With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&11" & strleft
If c = 2 Then
.CenterHeader = "&""Arial,Bold""&11" & strcentertop & Chr(10) & "&""Arial,Bold""&10Abstract"
ElseIf c = 3 Then
.CenterHeader = "&""Arial,Bold""&11" & strcentertop & Chr(10) & "&""Arial,Bold""&10Measurement Sheet"
ElseIf c = 4 Then
.CenterHeader = "&""Arial,Bold""&11" & strcentertop & Chr(10) & "&""Arial,Bold""&10Design Paper"
End If
.rightheader = "&""Arial""&10&BWard:&B " & " &""Arial""&10&BCat:&B " & Chr(10) & "&""Arial""&8&BPlace:&B " & Chr(10) & "&""Arial""&8Date: " & "&D"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&""Arial""&8Prepared By: Progin"
.LeftMargin = Application.InchesToPoints(1)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.93)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.39)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = False
If c <= 3 Then
' .Orientation = xlLandscape
.Orientation = xlPortrait
Else
.Orientation = xlLandscape
End If
' .Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = 14
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Next i
Application.ScreenUpdating = True
End Sub
Posted by Damon Ostrander on July 03, 2001 12:58 PM
Hello Nishith,
I am sorry to report that there is not much you can do about this. The reason for the slowness is that each time your code changes a PageSetup property, Excel goes and checks the printer driver to check if the printer supports the change. The driver actually polls the printer to determine if the change can be made. Thus in your case, Excel is polling the printer over a dozen times. If the printer is on a network with a lot of traffic, or it has a slow network connection--or even a slow direct connection to your computer, the page setup process can take a long time. This is why one printer's page setup can be fast, and another's slow. Excel must wait for the printer to respond.
The only way I have found to speed things up is to reduce to an absolute minimum the number of PageSetup properties I change. For example, setting the PrintHeadings property to False wastes time since False is the default value. Eliminate all statements that set PrintSetup properties to their default values.
I hope this helps.
Damon
