Use of ActiveSheet.PageSetup in print macro slows process.

E

ExcelAvenger

New Member
Joined
Oct 11, 2006
Messages
3
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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: 
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
 
Upvote 0
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.
 
Upvote 0
So your other macros use to run faster before this update ??
Unsure why, can you post the code that is running slower ?
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0
Re: Speeding up Page Setup

zakasnak said:
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!!
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Macro to automatically print
Replies
1
Views
1K
pure vito
P
A
  • Question Question
Printing a selected area macro
Replies
1
Views
892
FuNeS13
FuNeS13
C
  • Question Question
hide columns to print report using macro
Replies
2
Views
539
csenor
C
W
  • Question Question
How to automatically set print options for all sheets
Replies
5
Views
964
rlv01
R
B
  • Question Question
printing code needs edited
Replies
8
Views
871
bamaisgreat
B

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top