PageSetup - Speeding up - Code snippet Requested

[Tim Childs]

Hi

John Green has posted the excellent code below that speeds up Pagesetup in
later versions of Excel .

Is it possible within this macro to set the option Print to one page wide
and however many pages tall it requires? - I presume it is the variable
pscale that affects this?

Many thanks

Tim

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 = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
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

 

[Jim Cone]

Tim,
It has been awhile send I messed with xl4 page.setup code.
It is still faster then standard vba except for the Pages() property introduced in xl2007.

Checking my notes, Scale has three options:
1. A percentage number that increases/decreases the page size
2. True to fit the data to a single page
3. A two item horizontal array for pages wide by pages tall (#N/A for no limit)
Some experimenting is in order.
--
Jim Cone
Portland, Oregon USA
http://excelusergroup.org/media/

..
..
..

"Tim Childs" <[email protected]>
wrote in message
Hi
John Green has posted the excellent code below that speeds up Pagesetup in
later versions of Excel.
Is it possible within this macro to set the option Print to one page wide
and however many pages tall it requires? - I presume it is the variable
pscale that affects this?
Many thanks
Tim

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 = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
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

 

[GS]

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = ""
End With

 

[Tim Childs]

Hi Jim

thanks for that, can you get me started on what the 2-dim array will look
like e.g.
pscale = "1,#N/A"
I don't really know where to start.

thanks

Tim

 

[Jim Cone]

It required some experimenting. This works...
pscale = "{3,#N/A}"
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"Tim Childs" <[email protected]>
wrote in message
Hi Jim
thanks for that, can you get me started on what the 2-dim array will look
like e.g.
pscale = "1,#N/A"
I don't really know where to start.
thanks
Tim

"Jim Cone" <[email protected]>
wrote in message
news:[email protected]...

 

[Tim Childs]

Hi Jim

Genius. thanks v much for that - works a treat

bw

Tim