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
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...
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...
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
Regards,
Tom Ogilvy
John G <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...
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...
Dave Peterson <ec3...@msn.com> wrote in message
news:3BC0714A...@msn.com...
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...