PageSetup Performance Question

[bstobart]

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

 

[Jim Cone]

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
--
Jim 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