Solved

Updating Sources in Excel 2007 Spreadsheet freezing

Posted on 2009-04-13
10
567 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 23

Expert Comment

by:redrumkev
ID: 24129630
0
 

Author Comment

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

Author Comment

by:robo187
ID: 24129827
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:redrumkev
ID: 24129828
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
ID: 24129854
I have service pack 1 installed, I just figured out how to view it.
0
 
LVL 23

Expert Comment

by:redrumkev
ID: 24130183
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
ID: 24130329
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
ID: 24130634
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
ID: 31569480
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
ID: 24132781
Robert,

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

Also, thanks for the grade!
Kevin
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

630 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