Adapting Excel 4 Macro Page Setup for multiple pages
 

I've been attempting to use page setup within an Excel macro (Excel
2000) and have encountered the apparetnly very common problme that this
takes AGES to run.

Thanks to John Green's post on an earlier topic, I have almost got a
solution working based on Excel 4.

My outstanding issue is that I am trying to setup the page to be 1 page
wide by muliple pages tall. based on John's code example below, can
anyone suggest which field will control this?

Thanks

Phil

=================================
From: John Green )
Subject: About PageSetup..
Newsgroups: microsoft.public.excel.programming
View complete thread (10 articles)
Date: 2001-01-22 12:57:23 PST
PageSetup in VBA has always been a painfully slow process. If you can't

avoid having
to set these parameters, you can use the Excel 4 macro function,
PAGE.SETUP
to carry
out most of the PageSetup operations much more quickly. The following
two
macros are
almost equivalent, and should give you the clues you need to start
using
PAGE.SETUP.
You can download a full description of all the Excel 4 macro functions
from
Microsoft's web site:
Sub PS()
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
.LeftHeader = "My Company"
.CenterHeader = ""
.RightHeader = "&D / &T"
.LeftFooter = "Highly Confidential and Proprietary"
.CenterFooter = ""
.RightFooter = "Finance"
.LeftMargin = Application.InchesToPoints(0.54)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.36)
.HeaderMargin = Application.InchesToPoints(0.22)
.FooterMargin = Application.InchesToPoints(0.17)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
' .PrintQuality = 600 ' does not work with all the printers
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub
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
John Green (Excel MVP)
Sydney
Australia
=====================

Adapting Excel 4 Macro Page Setup for multiple pages
 

That would be the "Scale" argument or pScale as John Green uses it.
That is one argument that I never messed with. However, the instructions
list the following 3 options...
1. Specify a percentage.
2. Set scale to a two-item horizontal array, with the first item equal to
the width and she second item equal to the height.
Use #N/A for no constraint.
3. Use True or False, True fits the print area to a single page.

It's cut and fit time. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"SkiBumScot"
wrote in message
I've been attempting to use page setup within an Excel macro (Excel
2000) and have encountered the apparetnly very common problme that this
takes AGES to run.
Thanks to John Green's post on an earlier topic, I have almost got a
solution working based on Excel 4.
My outstanding issue is that I am trying to setup the page to be 1 page
wide by muliple pages tall. based on John's code example below, can
anyone suggest which field will control this?
Thanks
Phil

-snip-