Use of ActiveSheet.PageSetup in print macro slows process.

[ExcelAvenger]

I am using Excel 2003 SP2 to create a series of macros, one of which is to allow the user to print a portion of a worksheet created through another macro.

The macro detects the print area then uses ActiveSheet.PageSetup commance to set the various page setup options - margins, layout, etc.

I am on a network and have noticed through debug.print statements that each ActiveSheet.PageSetup commands takes about 5 seconds and since there are roughly 30 of them, it takes quite awhile before the printer dialogue box appears.

Here is the macro:

Sub PrintPricingDocument()
Sheets("Pricing Document").Select
Range("A1").Select
Selection.End(xlDown).Select
strLoc = Selection.Address
numLoc = Mid$(strLoc, 4, 2) 'gives the row number for the bottom of the print range
StrLoc2 = "$A$1:$G$" + CStr(numLoc)
Range("A1").Select
ActiveSheet.PageSetup.PrintArea = StrLoc2
Range("A1").Select
ActiveSheet.PageSetup.PrintTitleRows = ""
ActiveSheet.PageSetup.PrintTitleColumns = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftFooter = ""
ActiveSheet.PageSetup.CenterFooter = ""
ActiveSheet.PageSetup.RightFooter = ""
ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.TopMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.BottomMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
ActiveSheet.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
ActiveSheet.PageSetup.PrintHeadings = False
ActiveSheet.PageSetup.PrintGridlines = False
ActiveSheet.PageSetup.PrintComments = xlPrintNoComments
ActiveSheet.PageSetup.PrintQuality = 600
ActiveSheet.PageSetup.CenterHorizontally = True
ActiveSheet.PageSetup.CenterVertically = False
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.Draft = False
ActiveSheet.PageSetup.PaperSize = xlPaperLetter
ActiveSheet.PageSetup.FirstPageNumber = xlAutomatic
ActiveSheet.PageSetup.Order = xlDownThenOver
ActiveSheet.PageSetup.BlackAndWhite = False
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.PrintErrors = xlPrintErrorsDisplayed
Application.Dialogs(xlDialogPrint).Show
End Sub

I have tried the above statements in a With statement but no difference. I suspect this has something to do with the printer being on the company network?! Finally, I noticed that, in one instance after running my print macro, my other macros (invoked separately - not called by this macro) also slowed down - but this only happened once in several tests. Any ideas? Thanks in advance.

 

[Ivan F Moala]

Print setup can be slow depending on a number of things.
especially if is is a network printer.
I won't go into all of them but

A couple of things to try and speed things up
1) Don't display pagebreaks
.DisplayPageBreaks = False
2) Page setup code faster using
this method.

Uses old XL 4 macros function

Code:
'// NB: If any of the Variables are set wrong you will
'// Get NO error BUT it won't setup as you want.
ExecuteExcel4Macro ( _
    "Page.Setup(""Hello"",""Testing"",0.75,0.75,1,1,FALSE" & _
    ",FALSE,FALSE,FALSE,2,9,TRUE,100,1,FALSE,360,0.5,0.5,FALSE,FALSE)")

Where:
head =Hello
foot =Testing
left =0.75
right =0.75
top =1
bot =1
hdng =FALSE
grid =FALSE
h_cntr =FALSE
v_cntr =FALSE
orient =2
paper_size =9
scale =TRUE
pg_num =100
pg_order =1
bw_cells =FALSE
quality =300
head_margin =0.5
foot_margin =0.5
notes =FALSE
draft =FALSE

 

[ExcelAvenger]

Many thanks. That did the trick...however...

while my print macro now runs very fast...my other macros go really slow once this macro has been run.

 

[Ivan F Moala]

So your other macros use to run faster before this update ??
Unsure why, can you post the code that is running slower ?

 

[ExcelAvenger]

Wish I could....it's about 4500 lines.

I believe it has something to do with the network and not the code you sent me. I was having this problem intermittently using the orginal print macro I had. I searched on that topic and discovered that others have found printing to network printers has caused slowdowns running Excel. Unfortunately, none of these posts were particular to using macros and the suggestions to correct the issue in Excel didn't really apply.

Using the original print macro, I tried running it on another's PC and while it ran slow on their PC too, it didn't seem to cause the slow down with the other macros afterward like it did on my PC sometimes.

I am going to take the new code and try running on another PC and see if I have similar problems. Maybe it's environmental.

Again, many thanks for your help.

 

[zakasnak]

Re: Speeding up Page Setup

I have tried this code and can not get it to work for me!

The only think I can think would be that I set the print area before the page set up code.

ActiveSheet.PageSetup.PrintArea = "$A:$M"
ExecuteExcel4Macro ( _
"Page.Setup("","",0.75,0.75, 0.75, 0.75,FALSE" & _
",FALSE,TRUE,FALSE,2,1,TRUE,1,1,FALSE,600,0.5,0.5,FALSE,FALSE)")


I would also like for it to print 1 pg wide by as many tall as it needs... can that TRUE be something else? I read somewhere about an array? What is the syntax for that?

Thank you for any help you can gimme!!

 

[Peter_SSs]

Re: Speeding up Page Setup

I have tried this code and can not get it to work for me!

The only think I can think would be that I set the print area before the page set up code.

ActiveSheet.PageSetup.PrintArea = "$A:$M"
ExecuteExcel4Macro ( _
"Page.Setup("","",0.75,0.75, 0.75, 0.75,FALSE" & _
",FALSE,TRUE,FALSE,2,1,TRUE,1,1,FALSE,600,0.5,0.5,FALSE,FALSE)")


I would also like for it to print 1 pg wide by as many tall as it needs... can that TRUE be something else? I read somewhere about an array? What is the syntax for that?

Thank you for any help you can gimme!!

I'm not sure about the Excel4Macro code, but setting your print area to whole columns may slow navigation in your actual sheet. Just something to watch out for.

 

[ZVI]

See more details from Ivan F Moala in this thread: Use of ActiveSheet.PageSetup in print macro slows process

 

[ZVI]

See more details from Ivan F Moala in this thread: Use of ActiveSheet.PageSetup in print macro slows process

Sorry for the wrong place, it should be my response to the thread: Super Slow Macro HELP!!