Excell 2003 Page Setup Slow

[JT Lovell]

I read on Google that this is a known issue with Excel, but I couldn't find any suggestions that worked for 2003 or that made a noticeable impact. Here's the code I'm using:

With m_xlSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.Orientation = xlLandscape
End With

This code runs 4 times (once each for 4 worksheets). I'm running it from a VB DLL. I have already set

m_xlApp.ScreenUpdating = False

but this didn't make much of a difference -- likely because the entire workbook is not visible until the end of my code.

Without the page setup code, it takes 4 seconds to create a new workbook, 4 sheets, and populate them with data. With the page setup the time goes to 9 seconds.

Any ideas? Thanks,

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/pagesetup.html

 

[JT Lovell]

Thank you for the reply! I've tested the code using the Excel4 syntax and
here are the results:

6 seconds to run with no page setup formatting
11 seconds to run with VBA page setup formatting
9 seconds to run with Excel4 macro page setup formatting.

The code I'm using now is:

strMacro = "PAGE.SETUP(,,.5,.5,.5,.5,,,,,2,,,,,,,,,,)"
m_xlApp.ExecuteExcel4Macro strMacro

This is definitely an improvement, but is there any way to shave additional
time off? Would using a template with pre-setup page formatting improve the
overall speed, or would it break even given having to load the template?

Thanks for your help!

 

[Tom Ogilvy]

I would guess the template would be faster than either pagesetup or the xl4
macro. But why not just try it?

 

[jmac]

Whenever you create a new workbook and/or worksheet Excel bases it on
an existing template. There should be little or no speed difference
whether the template is the default or one you specify in your code.

jmac

 

[JT Lovell]

Good to know, thanks!

--
JT Lovell

Whenever you create a new workbook and/or worksheet Excel bases it on
an existing template. There should be little or no speed difference
whether the template is the default or one you specify in your code.

jmac

"JT Lovell" <[email protected]> wrote in message

 

[Dana DeLouis]

JT. I am a big believer in using Sheet templates to avoid the speed
problems that you mention. Another benefit is that since you are already
working on the template, you can include Column headings, Formulas, Text, &
Formatting, and just about anything else. This allows you to remove a lot
of vba code since the work is already done. The .Add command can also
insert more than one sheet with the Count option.
Adding a sheet based on a Template was an undocumented, but very useful
feature of Excel.
However, I just installed Excel 2003 today. It looks like Microsoft has
finally now documented this nice feature. Yeah!