Solved

Updating Sources in Excel 2007 Spreadsheet freezing

Posted on 2009-04-13
10
561 Views
Last Modified: 2012-05-06
I have an excel spreadsheet located on a server elsewhere. The way I access the document is via a network share located on that server setup as a network drive on my computer. My computer is a Windows XP Pro SP3 ThinkPad laptop and I am running Office 2007 suite. When I open that excel spreadsheet it takes a couple of seconds being that it's located on a server outside the building but when I try and update the sources after I'm through with the changes I made to the document, the spreadsheet freezes forcing me to stop the excel process and reopen excel again. There are about 12-13 other spreadsheets linked to this one spreadsheet all in the same share (Folder) located on the same server, so that shouldn't be an issue. I was wondering if it could be the excel 2007 app because on excel 2003 when trying the same updating process it doesn't freeze but it lags for a couple of seconds. Any help would be greatly appreciated.
0
Comment
Question by:robo187
  • 5
  • 5
10 Comments
 
LVL 23

Expert Comment

by:redrumkev
Comment Utility
0
 

Author Comment

by:robo187
Comment Utility
I'm checking on the SP I have installed for office.
0
 

Author Comment

by:robo187
Comment Utility
How can I tell what service pack I have? However I do remember downloading and installing a huge Office update when I first installed Office 2007.
0
 
LVL 23

Expert Comment

by:redrumkev
Comment Utility
Ok - you can locate the version by going (Office Button -> Excel Options (bottom Right) -> Resources (left pane) -> last line (right side) says SP1 MSO?

Kevin
0
 

Author Comment

by:robo187
Comment Utility
I have service pack 1 installed, I just figured out how to view it.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 23

Expert Comment

by:redrumkev
Comment Utility
robo187,

How are you doing?

What kind of formula's and updates are in use? I am wondering if there are in-efficiencies that cause the problem and ultimate crash of the App.

On the formula tap (then far right, Calculations) -> Calculation Options -> You can select manual. This will not auto calculate. Make your changes, then push "calculate now". Then save. See if this keeps it from crashing.

Are any of the linked spreadsheets missing?

Can you copy all of these spreadsheet files over to your local computer? If after copying them and running the spreadsheet locally, does it still crash?

Kevin
0
 

Author Comment

by:robo187
Comment Utility
Hey Kevin thanks for prompt responses. Updating is working now. It still takes awhile to complete the process once she highlights all the sources and clicks the update button. But the spreadsheet is not turning white and timing out and as I mentioned before with excel 2003 it never froze but it did take awhile to update. I also disabled Norton Antivirus Corporate edition client on her system for an application I was installing and forgot to reenable it. So it's also possible that Norton could have been interrupting the process with Office 2007 and not with Office 2003. I will alert you of my findings and thank you again for the help. Could it possibly be the connection to that server, which is in South Carolina. I'm in New Jersey.
0
 
LVL 23

Accepted Solution

by:
redrumkev earned 500 total points
Comment Utility
robo187,

Thanks for the update!

Could it possibly be the connection to that server, which is in South Carolina. I'm in New Jersey.

Very much so, but for a few reasons that are probably different then normal "web issues". Unlike a slow download, which just takes "time" to complete. Excel is actually going "back and forth" between your local (NJ) instance to do the calculations and the data which is in SC. So for example, Cell A1 is 10 and Cell B1 is 10 and Cell C1 says =sum(A1+B1), this data is "opened" on your NJ computer. But when you have a formula that says =sum(A1+Workbook_Invoice-Sheet1!B2) excel knows A1 is 10 (since you opened the copy on your NJ computer) but then it has to go via the network connection to SC, open Invoice.xls, go to sheet 1 and cell B2, get the value can then bring it back to fill in 10.

This is why I suggested copying all the files over, do the updates and the copy them all back. This way when it goes to look for "Invoice.xls" it is local, right there and the update should happen in just about "real time".

Another variable you have added is the Norton, which upon each request for data outside your network (when you get values for the updates in SC) the data going out or coming back, needs to be "scanned" so that does add to it. Since Excel really wasn't built for this across network use, I could see that Norton would see things as viruses (you are executing code remotely) and flag this, stop it, etc., causing excel to crash.

I don't have a lot of experience in your situation, but I do use remote systems daily. What I do is either run everything on the remote (log in using windows terminal services) so the copy of Excel, the files, everything would be on the SC computer. Basically the Internet connection between NJ and SC would serve as a very long mouse, monitor and keyboard cable.

Or, if the above is not an option, I copy everything over to my local computer, so that the entire internet connection for updating files/linking data, is removed from the equation.

Hope this makes sense and helps. I know I said that it really wasn't designed to do this, but if you can get it to work, then thats great. Excel 2003 (and the office 2003 suite, for that matter) was less picky, it would just run the link, if it took a while, so be it. But 2007 wants to verify everything and has a lot more catches, which are nice, but make for some crashes in implementations such as yours.

Sorry for the novel... let me know if you need anything else.
Kevin
0
 

Author Closing Comment

by:robo187
Comment Utility
kevin, your solution makes perfect since and that's what I'm going to suggest to the user. Copy everything over locally, make all the changes and then copy it back and overwrite the file. I appreciate you pretty much spelling everything out for me.

Thanks,

Robert
0
 
LVL 23

Expert Comment

by:redrumkev
Comment Utility
Robert,

No problem at all and thank you for the kind words!

Also, thanks for the grade!
Kevin
0

Featured Post

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.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

11 Experts available now in Live!

Get 1:1 Help Now