Solved

Excel 2007 Save and Save As Problems

Posted on 2012-03-20
18
517 Views
Last Modified: 2012-03-22
Hi

I'm having difficulty saving Excel files, both in 2007 and 97-2003 formats.  Some of the files are quite large, but not ridiculously so.  Excel frequently hangs during the save operation and, even when it does manage it, it's still really slow.  The problem is worse when saving across the network, but pretty bad when saving locally.  It's quicker if all the file links are broken but, obviously, this is not feasible for many files.  Typically, Excel crashes on me 5 or 6 times a day - more on manic reporting days.

I have heard/read various comments about it being caused by the Microsoft Office Validation, a security update, and so on.  I've also seen lots of pages that suggest registry changes.

I do remember that in my last role, the I.T. department came round and took one of the Microsoft updates off the machines to resolve this (can't remember which one).

Does anyone have a definitive answer that will definitely solve this?  The problem seems to apply to Access too, though that might be for a completely different reason.

I don't have administrator rights on this machine (damn them!) but if I have some kind of good reason why they should go down a particular path, I think I.T. will be prepared to try it.

I'm awarding 500 points, but won't be able to award them until the solution has been tried, tested and proved to work, so it may take a couple of days.

Thanks
Sarah
0
Comment
Question by:ScuzzyJo
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 10

Expert Comment

by:MadShiva
ID: 37741968
Dear,

You should tell the IT to have a look and run from admin the diagnose of office :

Diagnose and repair Microsoft Office

(You could also try from your account but maybe not all the problem will be corrected)

Regards
0
 

Author Comment

by:ScuzzyJo
ID: 37742003
Hi

Thanks for the answer but other users have the same problem so I don't think there's a problem with mine specifically.  Also, I definitely remember an update being taken off where I last worked which fixed the same or a similar problem.

Thanks
Sarah
0
 
LVL 5

Assisted Solution

by:joyofsharing
joyofsharing earned 400 total points
ID: 37742074
Dear Sarah,

Check the trail article on the same issue.
http://www.interworks.com/blogs/rclapp/2011/07/19/troubleshooting-excel-issues-after-microsoft-office-file-validation-office-h

Kindly keep us posted.


joyofsharing ( ^ _ ^ )
0
 
LVL 8

Assisted Solution

by:thomasdavis
thomasdavis earned 100 total points
ID: 37742106
http://support.microsoft.com/kb/2501584
Are you able to try this fix?
0
 
LVL 8

Expert Comment

by:thomasdavis
ID: 37742159
Also depending on what version of OS you are running whether Windows 7, Vista or XP if my documents is mapped using x:\location try to change this using \\server\location and also try adding the folder location in Document Properties where the excel file lives.
0
 

Author Comment

by:ScuzzyJo
ID: 37746402
All

Thanks to all of you.  I've looked at both the links, but no success... :-(

On the internetworks one, it only talks about Office11, which I don't have on my machine - I checked the registry and I definitely only have Office12.

On the Microsoft one, I couldn't find:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\FileValidation

I found it up to \Security but haven't got the FileValidation bit.

I also looked at the installed updates via the Control Panel and couldn't see KB2501584 anywhere.

Also, I can't run any of the Fix stuff as I don't have administrator rights on this machine.

I haven't tried the drive mappings bit yet, but will let you know the outcome of that one.  It also misbehaves when saving locally though.

Thanks
Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 37746407
Ooops!  Forgot to say - am on XP Service Pack 3.

Thanks
Sarah
0
 
LVL 10

Expert Comment

by:MadShiva
ID: 37746535
Dear Sarah,

Maybe now you could try to use the diagnose that I suggested?  :-)

Regards
0
 

Author Comment

by:ScuzzyJo
ID: 37746562
Hi MadShiva

I can't.  They're really strict on access rights here and I don't have the rights to run it.  Even though I said it wasn't just me having the problems, I would have tried it if I could have done.

Thanks
Sarah
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 5

Expert Comment

by:OOsorio
ID: 37747385
The actual update is KB2501584 as described previously. When in Programs and Features select, at the upper left, View Installed Updates to get a listing of only the updates.
0
 
LVL 5

Expert Comment

by:OOsorio
ID: 37747411
0
 

Author Comment

by:ScuzzyJo
ID: 37747540
Hi

I've tried those as Thomas Davis suggested earlier.  I'm running on XP, so don't have Programs and Features\View Installed Updates but I have checked it in a similar way on Add/Remove Programs with the Show Updates box ticked.  I can't see it in there.

Is there anything else that might cause this problem?

Thanks
Sarah
0
 
LVL 5

Expert Comment

by:OOsorio
ID: 37747629
Try disabling your AV for about 15 minutes while you work, test, the behaviour of the Excel files. Let's see if this helps. Maybe an update to the AV is causing this.
0
 
LVL 5

Expert Comment

by:joyofsharing
ID: 37748319
Dear Sarah,

Try Adding the EnableShellDataCaching value to the Microsoft Windows registry.

To do this, follow these steps:
1. Close your Office programs.
2. Click Start, and then click Run.
3. In the Open box, type regedit, and then click OK.
4. Locate, and then click to select the following registry key:
Office 2003
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\Open Find
Office 2007
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Open Find
5. After you select the key that is specified in step 4, point to New on the Edit menu, and then click DWORD Value.
6. Type EnableShellDataCaching, and then press ENTER.
7. Right-click EnableShellDataCaching, and then click Modify.
8. In the Value data box, type 1, and then click OK.
Note Any non-zero number in the Value data box will turn on caching. A zero number or blank (default) will turn off caching.
9. On the File menu, click Exit to quit Registry Editor.

http://support.microsoft.com/kb/833041

Kindly keep us posted.

joyofsharing ( ^ _ ^ )
0
 
LVL 5

Accepted Solution

by:
joyofsharing earned 400 total points
ID: 37748413
Dear Sarah,

Referring  to the comment ID 37742074, in Microsoft office 2007 we have Office 12.0 under the registry settings.

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Security\

In addition, as per the KB article we need to create the key FileValidation under the security key.

Try the EnableOnLoad registry entry to configure how you want Excel to handle opening workbooks for the OFV. By default, the EnableOnLoad entry is not present in the Windows registry. To add the EnableOnLoad entry to the Windows registry, follow these steps:
1. Exit Excel.
2. Click Start, click Run, type regedit, and then click OK.
3. Locate and then click to select the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\
4. After you select the key that is specified in step 3, point to New on the Edit menu, and then click Key.
5. Type Excel, and then press ENTER.
6. Select Excel, point to New on the Edit menu, and then click Key.
7. Type Security, and then press ENTER.
8. Select Security, point to New on the Edit menu, and then click Key.
9. Type FileValidation, and then press ENTER.
10. Select FileValidation, point to New on the Edit menu, and then click DWORD Value.
11. Type EnableOnLoad, and then press ENTER.
Note: The default value is 0 which disables the validation.
12. On the File menu, click Exit to quit Registry Editor.

http://support.microsoft.com/kb/2570623

Kindly keep us posted.


joyofsharing ( ^ _ ^ )
0
 

Author Comment

by:ScuzzyJo
ID: 37748531
Hi

I've done this and tried it on a couple of files and it's definitely quicker!  I need to test it a bit more to be sure, so will do that and will award the points tomorrow if they're all OK.

Interestingly, someone gave me an internal Citrix address (not really supposed to use that, but hey!) and it's like lightning!

Thanks
Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 37751800
Hi All

Thanks to all of you for all your help on this.  I've used joyofsharing's post and have opened and saved back a few large files.  It's still slow, but it hasn't been crashing at all, so I think that's a fantastic improvement.... fingers crossed!

I'm going to split the points as thomasdavis also came up with the same idea, but joyof sharing gets the lion's share for giving me detailed instructions on how to edit the registry for the fix.

Hope you're all OK with that.

Thanks
Sarah
0
 
LVL 5

Expert Comment

by:OOsorio
ID: 37752371
Big Yes. We all learn from the experience.
0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

20 Experts available now in Live!

Get 1:1 Help Now