Speed of PageSetup

59 views
Skip to first unread message

John G

unread,
Oct 7, 2001, 8:31:52 AM10/7/01
to
Dear all,

I have a list of data that creates 100 or so workbooks. The VBA code runs
very fast apart from the pagesetup stage which pauses for a few seconds. Is
their a way to speed this up.

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PrintGridlines = True
.CenterFooter = "Page &P of &N"
.RightFooter = "Grand Total Summary "
.CenterHorizontally = True
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.118110236220472)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.PrintTitleRows = "$1:$1"
End With

Thanks,

John G

Nick Hodge

unread,
Oct 7, 2001, 8:37:26 AM10/7/01
to
John

Code looks fine to me...page set-up is notoriously slow, unfortunately. I
don't think it's totally XL's fault, but suspect it's having to read printer
settings, etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_...@lineone.net


"John G" <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

SimPoh

unread,
Oct 7, 2001, 11:04:17 AM10/7/01
to
Hi John,

For every PageSetup property you run, time is required. If you could take
away some standard property like the margins, your code will run faster.

Regards
Sim Poh

John G <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

Dave Peterson

unread,
Oct 7, 2001, 11:14:18 AM10/7/01
to
John Green has posted this in the past. It uses the old xl4 macro language that
works more quickly than the VBA statements.

You should be able to pick apart the pieces that you need.

Sub PS4()
head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
pLeft = 0.54
pRight = 0.3
Top = 0.4
bot = 0.36
head_margin = 0.22
foot_margin = 0.17
hdng = False
grid = False
notes = False
quality = 600
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = False
pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," & pRight & ","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," & h_cntr &
","
pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," & pscale &
","
pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," & quality &
","
pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes & "," & Draft
& ")"

Application.ExecuteExcel4Macro pSetUp
End Sub

--

Dave Peterson
ec3...@msn.com

Tom Ogilvy

unread,
Oct 7, 2001, 11:28:31 AM10/7/01
to
Why not set up a single worksheet including the pagesetup. Then copy that
sheet each time.

Regards,
Tom Ogilvy


John G <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

Vasant Nanavati

unread,
Oct 7, 2001, 11:24:09 AM10/7/01
to
Hi John:

Try something like:

ExecuteExcel4Macro "PAGE.SETUP(" & Chr(34) & "" & Chr(34) & "," & Chr(34) &
"&CPage &P of &N&RGrand Total Summary" & Chr(34) &
",.15748031496063,.0118110236220472,.78740157480315,.78740157480315,FALSE,TR
UE,TRUE,FALSE,2,,100,,1,,,,,,)"

It's unbelievably fast compared to VBA. Watch for line wrapping--it's all
one line.

Regards,

Vasant.


John G <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

Vasant Nanavati

unread,
Oct 7, 2001, 11:27:04 AM10/7/01
to
You beat me to it again, Dave! Now who can't get a word in edgewise? :)

Dave Peterson <ec3...@msn.com> wrote in message
news:3BC0714A...@msn.com...

Dana DeLouis

unread,
Oct 7, 2001, 11:51:41 AM10/7/01
to
You may want to also consider using a workbook template.
Have most of your work done in this workbook, and save it as a template.
If you are importing data, Excel 2000 can import directly into this new
workbook.
That was a nice added feature in Excel 2000 because of this.

As a side note, I have noticed that different printers work at different
speeds which I believe is based on their Printer Driver.
But I have no references for that. Just an observation.
--
Dana DeLouis Windows Me & Office XP


"John G" <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

Reply all
Reply to author
Forward
0 new messages