• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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
0
patrickab
Asked:
patrickab
  • 13
  • 7
  • 2
  • +2
1 Solution
 
apresenceCommented:
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.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
 
patrickabAuthor Commented:
>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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> 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

0
 
Rory ArchibaldCommented:
Are all 5700 files workbooks?
0
 
patrickabAuthor Commented:
>Are all 5700 files workbooks?

Yep, they are.
0
 
Rory ArchibaldCommented:
Network share, or local drive?
0
 
patrickabAuthor Commented:
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
0
 
patrickabAuthor Commented:
>Network share, or local drive?

Local drive - private, domestic machine.
0
 
Rory ArchibaldCommented:
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?
0
 
patrickabAuthor Commented:
>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.
0
 
Rory ArchibaldCommented:
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.
0
 
patrickabAuthor Commented:
>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.
0
 
Rory ArchibaldCommented:
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)
0
 
calacucciaCommented:
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)
0
 
patrickabAuthor Commented:
>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.
0
 
patrickabAuthor Commented:
>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.
0
 
Rory ArchibaldCommented:
See if this registry tweak solves it: http://support.microsoft.com/kb/818792
0
 
patrickabAuthor Commented:
Thanks for that link I'll see if I can tweak the registry tomorrow. I'll let you know the outcome after that.
0
 
patrickabAuthor Commented:
Not forgotten about this - just haven't got round to trying the Registry fix. I'll be back...
0
 
patrickabAuthor Commented:
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?
0
 
Rory ArchibaldCommented:
You could always set up a restore point, then install the hotfix?
0
 
patrickabAuthor Commented:
I will consider that as an option, meantime I am going to accept your comment as the solution.
0
 
patrickabAuthor Commented:
Thanks for your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 7
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now