Excel 2002 behaviour
Hi Experts,
When I first open Excel 2002 and want then to open an existing file I press the 'Open' icon. I then have to wait for about 40 seconds for it to populate the list of available files - about 5700. If on the other I instead press the 'SaveAs' icon the file dialogue appears and lists the files in about 6 seconds. I can then close the SaveAs dialogue box, press the 'Open' icon and the list of files is available in 1 second - so speeding up the whole process.
If I then close Excel and re-open it, press the Open icon, the files are listed in 1 second. That holds true for the rest of the day.
So my question is why is one way faster than another? What is happening and why does it behave that way?
Clearly this question is just for interest and is not crucial for anything other than to satisfy my curiosity.
Patrick
When I first open Excel 2002 and want then to open an existing file I press the 'Open' icon. I then have to wait for about 40 seconds for it to populate the list of available files - about 5700. If on the other I instead press the 'SaveAs' icon the file dialogue appears and lists the files in about 6 seconds. I can then close the SaveAs dialogue box, press the 'Open' icon and the list of files is available in 1 second - so speeding up the whole process.
If I then close Excel and re-open it, press the Open icon, the files are listed in 1 second. That holds true for the rest of the day.
So my question is why is one way faster than another? What is happening and why does it behave that way?
Clearly this question is just for interest and is not crucial for anything other than to satisfy my curiosity.
Patrick
Most likely the first time Excel has to pull the file list it has to physically scan the hard drive for the files. Once it's done that one time, then the data is cached in memory, so the next time you open either the open or save dialogs, it pulls the cache data and does not have to hit the physical disk.
Patrick,
when you use the file open dialog, there are options to show the file in preview etc. Maybe preparing for all these options takes a little while to organise. Saving As, on the other hand, just requires a list of file types, and contributes to the cache of files that will show in the Open dialog.
Just stabbing in the dark, trying to find excuses for an Excel version 8 years out of date ... (oops, ellipses)
cheers, teylyn
when you use the file open dialog, there are options to show the file in preview etc. Maybe preparing for all these options takes a little while to organise. Saving As, on the other hand, just requires a list of file types, and contributes to the cache of files that will show in the Open dialog.
Just stabbing in the dark, trying to find excuses for an Excel version 8 years out of date ... (oops, ellipses)
cheers, teylyn
ASKER
>Most likely the first time Excel has to pull the file list it has to physically scan the hard drive for the files. Once it's done that one time, then the data is cached in memory, so the next time you open either the open or save dialogs, it pulls the cache data and does not have to hit the physical disk.
Sorry but that misses the whole point of my question. It's, why the difference between the two methods to achieve exactly the same thing.
>when you use the file open dialog, there are options to show the file in preview etc. Maybe preparing for all these options takes a little while to organise. Saving As, on the other hand, just requires a list of file types, and contributes to the cache of files that will show in the Open dialog.
The lists of files are just the same both ways - just a simple list. So I don't think that really explains what's happening or why.
>Just stabbing in the dark, trying to find excuses for an Excel version 8 years out of date
Well, it's certainly 8 years old - but is it out of date? It serves me well for the moment. Other than for Experts Exchange I have virtually no use for Excel and certainly none for VBA these days.
Patrick
Sorry but that misses the whole point of my question. It's, why the difference between the two methods to achieve exactly the same thing.
>when you use the file open dialog, there are options to show the file in preview etc. Maybe preparing for all these options takes a little while to organise. Saving As, on the other hand, just requires a list of file types, and contributes to the cache of files that will show in the Open dialog.
The lists of files are just the same both ways - just a simple list. So I don't think that really explains what's happening or why.
>Just stabbing in the dark, trying to find excuses for an Excel version 8 years out of date
Well, it's certainly 8 years old - but is it out of date? It serves me well for the moment. Other than for Experts Exchange I have virtually no use for Excel and certainly none for VBA these days.
Patrick
>> Well, it's certainly 8 years old - but is it out of date?
Whatever quirks Excel 2002 may show, Microsoft won't do anything about it, even if it is a bug they know about. MS only support the current version plus the last one before that, which currently means that they support 2010 and 2007. You can't even expect any more patches or updates for Excel 2003, which must be one of the versions with the longest active life time in the user community, and is probably still being used by a major percentage of the Office clientele.
Office 2002 was, comparatively, a rather short-lived version. Office 2003 introduced quite a few new features, although in the same user interface and most changes being under the hood, and is probably more commonly used than 2002 these days.
I'm not trying to explain my way out of not being able to supply a solution to your problem, but, let's face it: at some point in time it's probably better to upgrade to a more recent version and get rid of the old problems thus.
What did a top-of-the-line mobile phone look like in 2002? Would it still be able to compete with a low-standard current model?
cheers, teylyn
Whatever quirks Excel 2002 may show, Microsoft won't do anything about it, even if it is a bug they know about. MS only support the current version plus the last one before that, which currently means that they support 2010 and 2007. You can't even expect any more patches or updates for Excel 2003, which must be one of the versions with the longest active life time in the user community, and is probably still being used by a major percentage of the Office clientele.
Office 2002 was, comparatively, a rather short-lived version. Office 2003 introduced quite a few new features, although in the same user interface and most changes being under the hood, and is probably more commonly used than 2002 these days.
I'm not trying to explain my way out of not being able to supply a solution to your problem, but, let's face it: at some point in time it's probably better to upgrade to a more recent version and get rid of the old problems thus.
What did a top-of-the-line mobile phone look like in 2002? Would it still be able to compete with a low-standard current model?
cheers, teylyn
Are all 5700 files workbooks?
ASKER
>Are all 5700 files workbooks?
Yep, they are.
Yep, they are.
Network share, or local drive?
ASKER
teylyn,
I think you are missing the point of my question - must be the excessive numbers of sheep in your part of the world. I am not expecting a solution, I am certainly not expecting MS to fix it and it is certainly not a problem that makes my edition of Excel unusable. And it certainly doesn't merit any money being thrown at it by way of an upgrade. I may upgrade in due course but the decision to do that would be more to do with buying a new computer than any 'problem' with Excel - which it isn't really. I am not even interested in comparisons with the latest edition of Excel as that is not relevant to my question.
I only asked the question to see if anyone could explain the behaviour.
Patrick
I think you are missing the point of my question - must be the excessive numbers of sheep in your part of the world. I am not expecting a solution, I am certainly not expecting MS to fix it and it is certainly not a problem that makes my edition of Excel unusable. And it certainly doesn't merit any money being thrown at it by way of an upgrade. I may upgrade in due course but the decision to do that would be more to do with buying a new computer than any 'problem' with Excel - which it isn't really. I am not even interested in comparisons with the latest edition of Excel as that is not relevant to my question.
I only asked the question to see if anyone could explain the behaviour.
Patrick
ASKER
>Network share, or local drive?
Local drive - private, domestic machine.
Local drive - private, domestic machine.
I suspect (even though it's local) that it might be the Autoselect feature. When you use the File-Open dialog, is one of the files automatically selected for you?
ASKER
>When you use the File-Open dialog, is one of the files automatically selected for you?
No file is automatically selected for me. The file dialogue box is blank. Perhaps the pre-selection is a feature of later editions.
No file is automatically selected for me. The file dialogue box is blank. Perhaps the pre-selection is a feature of later editions.
Nope - it was specifically disabled in later editions because of poor performance of the Open/Find dialog.
Note: I'm not suggesting that the filename box is populated, just that one of the files is selected and the Open button is enabled by default.
Note: I'm not suggesting that the filename box is populated, just that one of the files is selected and the Open button is enabled by default.
ASKER
>Note: I'm not suggesting that the filename box is populated, just that one of the files is selected and the Open button is enabled by default.
Ah, I understand. I'll have a look tomorrow after boot-up - takes approx 10mins so I'm not going to do it now.
Ah, I understand. I'll have a look tomorrow after boot-up - takes approx 10mins so I'm not going to do it now.
If it does turn out to be that, there may be a registry fix (I don't have 2002 installed anywhere to test on, sadly)
Could be an unavailable netword drive which you don't use anymore and does not bother you but does creates this behaviour on File Open (stab in the semi-dark)
ASKER
>Could be an unavailable netword drive which you don't use anymore and does not bother you but does creates this behaviour on File Open (stab in the semi-dark)
Nope. I'm not on a network and don't have any unused drives. Apart from that it does not explain the different behaviour of SaveAs as opposed to Open.
Nope. I'm not on a network and don't have any unused drives. Apart from that it does not explain the different behaviour of SaveAs as opposed to Open.
ASKER
>Note: I'm not suggesting that the filename box is populated, just that one of the files is selected and the Open button is enabled by default.
That does appear to be the case when first used each day.
That does appear to be the case when first used each day.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for that link I'll see if I can tweak the registry tomorrow. I'll let you know the outcome after that.
ASKER
Not forgotten about this - just haven't got round to trying the Registry fix. I'll be back...
ASKER
It's a bit scary because the fix notes say that once installed the fix cannot be uninstalled. I've downloaded it and am now not sure I want to take the risks it outlines in its ReadMe file.
I wonder if there are any on-line comments from those that have tried it?
I wonder if there are any on-line comments from those that have tried it?
You could always set up a restore point, then install the hotfix?
ASKER
I will consider that as an option, meantime I am going to accept your comment as the solution.
ASKER
Thanks for your help.