Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Updating Sources in Excel 2007 Spreadsheet freezing

Posted on 2009-04-13
10
Medium Priority
?
569 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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

Technology Partners: 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!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

670 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