Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extreme opening time of workbook (15 minutes)
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 Auto_Open 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extreme opening time of workbook (15 minutes)
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. "Alex van der Spek" wrote: 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 Auto_Open 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extreme opening time of workbook (15 minutes)
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) "Alex van der Spek" wrote in message 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 Auto_Open 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extreme opening time of workbook (15 minutes)
Great stuff, thanks a bunch. Deleting everything (12500 files) in the
"right" temp folder did the trick. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening workbook on the same page every time | Excel Worksheet Functions | |||
workbook opening takes 2 minutes | Excel Discussion (Misc queries) | |||
Extreme, time-consuming processing | Excel Discussion (Misc queries) | |||
Code for opening a workbook at a specific time!?! | Excel Discussion (Misc queries) | |||
Remove password at the time of opening the workbook | Excel Programming |