Solved

Excel 2002 behaviour

Posted on 2010-08-31
24
293 Views
Last Modified: 2012-05-10
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
Comment
Question by:patrickab
  • 13
  • 7
  • 2
  • +2
24 Comments
 
LVL 6

Expert Comment

by:apresence
ID: 33565876
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
 
LVL 50

Expert Comment

by:teylyn
ID: 33565896
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
 
LVL 45

Author Comment

by:patrickab
ID: 33566075
>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
 
LVL 50

Expert Comment

by:teylyn
ID: 33566186
>> 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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33566677
Are all 5700 files workbooks?
0
 
LVL 45

Author Comment

by:patrickab
ID: 33566881
>Are all 5700 files workbooks?

Yep, they are.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33566928
Network share, or local drive?
0
 
LVL 45

Author Comment

by:patrickab
ID: 33566971
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
 
LVL 45

Author Comment

by:patrickab
ID: 33567047
>Network share, or local drive?

Local drive - private, domestic machine.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33567109
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
 
LVL 45

Author Comment

by:patrickab
ID: 33567126
>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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33567176
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Author Comment

by:patrickab
ID: 33567225
>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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33567241
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33567425
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
 
LVL 45

Author Comment

by:patrickab
ID: 33567949
>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
 
LVL 45

Author Comment

by:patrickab
ID: 33581488
>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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33581600
See if this registry tweak solves it: http://support.microsoft.com/kb/818792
0
 
LVL 45

Author Comment

by:patrickab
ID: 33582251
Thanks for that link I'll see if I can tweak the registry tomorrow. I'll let you know the outcome after that.
0
 
LVL 45

Author Comment

by:patrickab
ID: 33616115
Not forgotten about this - just haven't got round to trying the Registry fix. I'll be back...
0
 
LVL 45

Author Comment

by:patrickab
ID: 33616368
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33616469
You could always set up a restore point, then install the hotfix?
0
 
LVL 45

Author Comment

by:patrickab
ID: 33618254
I will consider that as an option, meantime I am going to accept your comment as the solution.
0
 
LVL 45

Author Closing Comment

by:patrickab
ID: 33618271
Thanks for your help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now