Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ===================== |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When using show pages in a pivot table can I keep the page setup? | Excel Discussion (Misc queries) | |||
Page Setup Across Multiple Worksheets | Excel Worksheet Functions | |||
Page Setup for multiple worksheets macro problem | Excel Discussion (Misc queries) | |||
Excel Print Page setup macro... | Excel Programming | |||
Page setup for multiple pages | Excel Programming |