Excel 2000 macro for page format slow

[sgadams]

HI all -

I have a laptop with Windows XP professional and Office 2000. At other
times I had Office XP and Office 2003 installed but have removed those
via Add/Remove programs and also gone to the file system as well as the
registry to remove traces of Office XP/Office 2003. I have also
removed and re-installed Office 2000 as well as the SP-3.

Anyway, I have a macro I had created and saved as an add-in to do
several Page Setup actions. This macro ran in about 20 seconds before
all of my upgrades/changes. Now it takes about 2-3 minutes. My other
machine (PC) has Windows XP Professional and Office 2000 (SP-3) and the
macro still takes 20 seconds as expected after the SP-3 update.

I even upgraded the laptop from 256MB to 512MB memory to match the PC
but the laptop is still slow with the macro. I notice that when I look
at task manager while running the macro on the laptop, in the programs
tab it shows 2 instances of my excel file (but only one EXCEL.EXE
process). But the two instances only come up when I run the macro on
the laptop and not on the PC. (And if I just open the file on the
laptop without running the macro it just has one instance of the file.)

Does anyone have any suggestions on how to proceed? I had seen and
tried several remedies for slow Excel 2000 after SP-3 update. I
suspect that something about the Office 2003 and Office XP is still
hanging around and causing probs. I have also gone to the macro and
re-saved and deployed the add-in but still no luck.

Or is there a better way other than the macro/add-in approach to format
an Excel spreadsheet?

Thanks!
S

 

[Tom Ogilvy]

Speed of PageSetup command is heavily dependent on the default printer driver
selected at the time they are issued. Even if you don't have your PC
accessible to the printer on the faster machine, set up the print driver for
that printer using Add printer. then have your code select that before the
page setup and if necessary, go back to the original after. You can get the
current with

s = Appliction.ActivePrinter

You would then set the "dummy" driver as the activeprinter using the same
command/format.

Do the page setup. Switch back if necessary.

 

[Tom Ogilvy]

Also, if you recorded the macro, many of the settings recorded are the
default and there is no need to have them in your code. Each command is a
separate call to the print driver, so the fewer the better.