 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / December 2007PageSetup Performance Question
I've written a MS-Project VBA macro to export data to MS-Excel. The macro works fine. The performance is good...until I get to the PageSetup section, then the performance drops. The entire macro takes 26 seconds to run, but 16 seconds of that are used up on the PageSetup command, which seems strange. I've included the PageSetup section below. As you can see I've commented out the portions that are not needed. Any ideas? ---------------------------------- Set ExcelApp = CreateObject("Excel.application") ' Open Excel. Default is ExcelApp.visible=false ... ExcelApp.ScreenUpdating = False ExcelApp.EnableEvents = False ... .DisplayPageBreaks = False ' I've read that this line should help but it didn't With .PageSetup .PrintArea = AllDataRange.Address .PrintTitleRows = "$2:$2" ' .PrintTitleColumns = "" ' .LeftHeader = "" ' .CenterHeader = "" ' .RightHeader = "" .LeftFooter = "&8&Z&F" .CenterFooter = "Page &P of &N" .RightFooter = "Printed: &D &T" .LeftMargin = ExcelApp.InchesToPoints(0.25) .RightMargin = ExcelApp.InchesToPoints(0.25) .TopMargin = ExcelApp.InchesToPoints(0.5) .BottomMargin = ExcelApp.InchesToPoints(0.5) .HeaderMargin = ExcelApp.InchesToPoints(0.25) .FooterMargin = ExcelApp.InchesToPoints(0.25) ' .PrintHeadings = False ' .PrintGridlines = False ' .PrintComments = xlPrintNoComments ' .PrintQuality = 600 ' .CenterHorizontally = False ' .CenterVertically = False .Orientation = xlLandscape ' .Draft = False .PaperSize = xlPaperLetter ' .FirstPageNumber = xlAutomatic ' .Order = xlDownThenOver ' .BlackAndWhite = False ' .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False ' .PrintErrors = xlPrintErrorsDisplayed End With ' .PageSetup PageSetUp can take its own time, however your 16 seconds seems excessive. Do you have an object reference set to the worksheet? That might help and it is a good idea anyway. You can use the actual point settings instead of InchesToPoints. There are 72 point to the inch, so your left margin, for instance, could be... .LeftMargin = 18 Make sure you prune all settings you don't need... .CenterHeader and .CenterFooter can go and others? Finally, if all else fails, XL4 macro code is faster than VBA with PageSetUp. See... http://www.mcgimpsey.com/excel/udfs/pagesetup.html  SignatureJim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "bstobart" wrote in message I've written a MS-Project VBA macro to export data to MS-Excel. The macro works fine. The performance is good...until I get to the PageSetup section, then the performance drops. The entire macro takes 26 seconds to run, but 16 seconds of that are used up on the PageSetup command, which seems strange. I've included the PageSetup section below. As you can see I've commented out the portions that are not needed. Any ideas? ---------------------------------- Set ExcelApp = CreateObject("Excel.application") ' Open Excel. Default is ExcelApp.visible=false ... ExcelApp.ScreenUpdating = False ExcelApp.EnableEvents = False ... .DisplayPageBreaks = False ' I've read that this line should help but it didn't With .PageSetup .PrintArea = AllDataRange.Address .PrintTitleRows = "$2:$2" ' .PrintTitleColumns = "" ' .LeftHeader = "" ' .CenterHeader = "" ' .RightHeader = "" .LeftFooter = "&8&Z&F" .CenterFooter = "Page &P of &N" .RightFooter = "Printed: &D &T" .LeftMargin = ExcelApp.InchesToPoints(0.25) .RightMargin = ExcelApp.InchesToPoints(0.25) .TopMargin = ExcelApp.InchesToPoints(0.5) .BottomMargin = ExcelApp.InchesToPoints(0.5) .HeaderMargin = ExcelApp.InchesToPoints(0.25) .FooterMargin = ExcelApp.InchesToPoints(0.25) ' .PrintHeadings = False ' .PrintGridlines = False ' .PrintComments = xlPrintNoComments ' .PrintQuality = 600 ' .CenterHorizontally = False ' .CenterVertically = False .Orientation = xlLandscape ' .Draft = False .PaperSize = xlPaperLetter ' .FirstPageNumber = xlAutomatic ' .Order = xlDownThenOver ' .BlackAndWhite = False ' .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False ' .PrintErrors = xlPrintErrorsDisplayed End With ' .PageSetup |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.