Extreme opening time of workbook (15 minutes)

[Alex van der Spek]

I have 1.5 Mbyte workbook controlling data acquisition (VXI/GPIB) which
uses VBA and calls to DLL's containing HP low level routines.

Over time opening this workbook became more time consuming, resulting in a
staggering 15 minutes right now.

There are no Autpen macros. The Open event is not used on workbook or
sheet level. Disabling all macros does not improve the situation. Closing
the workbook takes just as long as opening it. There is virtually nothing
to recalculate as the workbook (master copy) is empty except for some
boiler plate text, formatting and the VBA code. Switching to manual recalc
does not help.

The PC is not out of diskspace (33% free), defragmentation status is OK.
On a different PC the workbook opens in a flash.

Does anyone know of diagnostics code to be put in the open event so that I
can see what is going on whilst the workbook is being opened.

Thanks a bunch.

 

[Joel]

Occasionally workbook get corrupted and you never find the cause. You need
to evaluate if it is going to be faster to copy everything to a new workbook
or try to find the source of the problem.

I would one piece at a time copy from old workbook to new workbook. Copy
each worksheet one at a time and then any macros. Inbetween save and open
the file to make sure you didn't copy the problem from the old workbook to
the new.

It is worth spending 10 minutes doing the copying than 8 hours trying to
find the problem.

 

[Jim Cone]

If it opens "in a flash" on another machine, it sounds like your machine
could be at fault not the workbook.
If your Temp folder has several thousand files and or the Excel *.xlb file size is much
over 30/40 kb then you can have problems opening Excel.
A couple of tune-ups you can quickly try are...

1. Clean out (delete everything) the Windows temp folder... Start | Run | %temp%
2. Uncheck all add-ins in Tools | Add-ins
Close Excel and find and delete the *.xlb file, usually found in
"C:\Documents and Settings\some name\Application Data\Microsoft\Excel".
You will lose all customizations to your menus/toolbars.
When you restart Excel the default menus/toolbars will rebuilt themselves.
(Note: just resetting the menus/toolbars will not do the job)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



I have 1.5 Mbyte workbook controlling data acquisition (VXI/GPIB) which
uses VBA and calls to DLL's containing HP low level routines.

Over time opening this workbook became more time consuming, resulting in a
staggering 15 minutes right now.

There are no Autpen macros. The Open event is not used on workbook or
sheet level. Disabling all macros does not improve the situation. Closing
the workbook takes just as long as opening it. There is virtually nothing
to recalculate as the workbook (master copy) is empty except for some
boiler plate text, formatting and the VBA code. Switching to manual recalc
does not help.

The PC is not out of diskspace (33% free), defragmentation status is OK.
On a different PC the workbook opens in a flash.

Does anyone know of diagnostics code to be put in the open event so that I
can see what is going on whilst the workbook is being opened.

Thanks a bunch.

 

[Alex van der Spek]

Great stuff, thanks a bunch. Deleting everything (>12500 files) in the
"right" temp folder did the trick.