Solved

Why don't external links work in Excel 2010 like they do in Excel 2007?

Posted on 2010-09-13
11
4,016 Views
Last Modified: 2012-05-10
I have an excel file (File A) that's downloaded from a company resource server.  It has cells which reference data in a different file (File B) on that server.  The File A cells reference the File B data in a format that looks like this:

='http://www.ourdomain.com/Resources/Our_Resources/General/Pricing_and_Shipping/[File_B.xlsx]Sheet1'!C4

When File A opens, it's not updating the value in this cell with the value from File_B.  I've turned on every Excel 2010 option I can find that should allow external links to work.  This works fine in Excel 2007.

HELP! (please?)

Scott

0
Comment
Question by:ScottPSutherland
  • 5
  • 4
11 Comments
 
LVL 7

Expert Comment

by:hippohood
ID: 33665307
Does it report a #REF! error or just doesn't update the value?
0
 
LVL 3

Expert Comment

by:shariati
ID: 33665337
It works fine for me .. I just tried it ... let us know what error are you getting.
0
 
LVL 7

Expert Comment

by:hippohood
ID: 33665399
And how do you know if this is the version problem? Are sure it is not a naming problem (e.g., in the source file .xlsx vs. .xls extension)?
BTW, the links can not be updated if the source file can not be open
0
 
LVL 7

Expert Comment

by:hippohood
ID: 33665445
Sorry for multi-line response, I hde problem with Submit button.
To see the error you have you shall go to Data\Edit Links, select the link to the source file of interest and press Update Values. It will show you a warning in that window, which would help to undersatnd the problem (e.g., Open source to update values)
0
 

Author Comment

by:ScottPSutherland
ID: 33674094
Greetings!  Not getting any #REF errors.  I'm assuming it's a version problem because it works fine with Excel 2007.  In 2007, all I need to do is set these Advanced Excel options:

1)  Update links to other documents ("When calculating this workbook" group) = ON (checked)
2)  Ask to update automatic links ("General" group) = OFF (unchecked)

I even have External Content Trust Center settings set to:

1)  Disable all data connections
2)  Disable automatic update of workbook links

And it still works (I guess based on the Advanced options I have set???)

Let me fully explain how I've got these files set up.  There's one file that contains cost data (we'll call this the "Costs" file).  It's a single worksheet file that management accesses when they need to update cost data from which selling prices are deduced.  They download the current Costs file from the server, make the changes, send the updated file to me, and I upload to the server.

There's another file (let's call it the "Pricing" file) that has a "Costs" worksheet that looks identical in terms of format, structure & content, but whose cells have formulas which reference the "Costs" file, so that the latest cost data will be pulled in for pricing.  One of the actual links looks like this:  

='http://www.durableproductsgroup.com/Resources/DWP_Resources/DWP_General/Pricing_and_Shipping/[DWP_Price_Sheet_Cost_Inputs.xlsx]Cost_Inputs'!E9

What's interesting to me is that even in 2010, there are actual cost values being pulled into these cells.  I can tell because those values are used in many other worksheets within the "Pricing" file.  It's just that the Pricing file is not pulling the LATEST vales from the Costs file.  It's almost as if the Pricing file is referring to some old cached version of the Costs file.  But I've tried clearing the Firefox cache and the same thing happens.  On the machine with Excel 2007 on it (where everything works fine), I don't clear the Firefox cache and everything still works great.  So I'm thinking it's not a Firefox cache thing.  It's behaving as though Excel 2010 has some sort of cache itself, and that it's pulling from an old file.

The only other thing I can think to tell you is that all these files are in a password-protected directory.  When users attempt to get to the "Resource Center" where these files are stored, they must enter a username & password.  Only after they've done so do they even get to the links that download the Costs and/or Pricing files.  In Excel 2007, I've noticed that every now and then (it's definitely not every time), the user will be prompted from WITHIN EXCEL to enter the username & password when they open the Pricing file.  I'm assuming it's because the Pricing file is pulling data from the Casts file (which is also on the password-protected directory).  Why it should happen like this sometimes and not others, I don't know.  Maybe the "active login" to the directory has expired between the time they opened the Resource Center page (which has the links to the Costs & Pricing files) and the time they actually hit the link to open the Pricing file.

But while I've seen this happen from time to time in Excel 2007, I've NEVER seen it happen within Excel 2010.  So I'm wondering if something about the password-protected directory is getting in the way with 2010, and it's never asking for username/password, but simply deciding not to access the file.  Still, that doesn't answer the question of where it's getting the old set of Cost values it's obviously using.

I don't know if any of this helps at all, but at least it's a few more symptoms that may shed some light.

I'll go get the information from the Data > Edit links on 2010 and send that in a bit.

Thanks!!

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ScottPSutherland
ID: 33674321
OK, here's Edit Links info.  There are two screenshots:  one of Edit Links info from 2007 right after opening the file (data has already been pulled and correctly updated by the time I even click the "Edit Links" button), and one from 2010, also right after opening the file (data has NOT been updated).

As you can see, the Edit Links info is identical.  HOWEVER, in 2007, when I click the Edit Links "Check Status" button, both file statuses change to "OK".  On 2010, when I click the "Check Status" button, both files come up as "Error:  Source not found".   Obviously something is preventing 2010 from accessing those files.  But again -- how does it get ANY values in the Costs worksheet if it can't access the Costs file???  I'd think the values would be blank at best.

Thanks.
edit-links-2007.jpg
edit-links-2010.png
0
 
LVL 7

Expert Comment

by:hippohood
ID: 33678211
First of all - no, in the abcense of the file the cells would not be blank. They would either show the error or old value, which you got at the last successful update. The following issues could be the problem:
1) you may have security settings which do not allow external links. Go to the Trust Center (Office button -> Trust Center) and add that location to the Trusted Locations and change the External Content behaviour
2) you don't have same priveledges for different user accounts. Try to open the file manually. If it is the case - contact your system administrator
3) the naming conventions are wrong. If your new sheet refers to Named Ranges (not the actual A1 addresses) in the older version sheet that could be an issue
 
0
 
LVL 7

Expert Comment

by:hippohood
ID: 33683004
RE: "When users attempt to get to the "Resource Center" where these files are stored, they must enter a username & password.  Only after they've done so do they even get to the links that download the Costs and/or Pricing files."
The above means you might be using RMS or analog. That means that you have to connect to a licensing server to acquire your permission level for a given file.
This system is present in both 2007 and 2010, but you have to install a client Windows component for this to work. You may need to check with your system administartor if you have all needed RMS Client software installed.
Also, if you have different Windows accounts - ensure both of them have same permissions. However, in this case I would expect you to see a message box, which says that you don't have credentilas to access the document.
0
 

Author Comment

by:ScottPSutherland
ID: 33683058
"First of all - no, in the abcense of the file the cells would not be blank. They would either show the error or old value, which you got at the last successful update. "

The file has NEVER been successful in accessing the links and has never been saved on this user's machine.

"1) you may have security settings which do not allow external links. Go to the Trust Center (Office button -> Trust Center) and add that location to the Trusted Locations and change the External Content behaviour "

Tried it.  Added this to the "trusted locations":  http://www.durableproductsgroup.com/Resources/DWP_Resources/DWP_General/Pricing_and_Shipping/ and checked box to allow locations on network.  Settings also allow external content/links and macros (just to be sure).  Same result.

"2) you don't have same priveledges for different user accounts. Try to open the file manually. If it is the case - contact your system administrator"
The user can access the file in question directly using the URL.  I even copied the contents of one of the links that's not getting updated, removed the brackets around file name, removed the cell reference at the end, removed the equals sign at the beginning and pasted the resultant URL into his browser (just to make sure there were no weird typos or anything).  The file opened right up.

"3) the naming conventions are wrong. If your new sheet refers to Named Ranges (not the actual A1 addresses) in the older version sheet that could be an issue"

Not sure what you're referring to when you're talking about my "naming conventions".  Please clarify for me.

Also, bear in mind that all of this works flawlessly in Excel 2007.  I've tested on multiple users' machines.  So if what you suggest as a possible problem should ALSO be a problem for Excel 2007, that's not the problem.  If it was, then it wouldn't be working right in Excel 2007.

Thanks for your (continued) help!!

Scott
0
 

Accepted Solution

by:
ScottPSutherland earned 0 total points
ID: 33737061
I moved the "inaccessible" files to a different directory that was NOT password-protected, and everything works fine.  I have concluded, therefore, that the problem is related to accessing files on a password-protected directory.  As mentioned in a prior comment, Excel 2007 asks for a username & password when attempting to access these files.  Given those, it accesses them fine.  Excel; 2010 never even asked for username & password.  It just went directly to "fail".  So I guess I will submit a different question around how one gets Excel 2010 to ask for username & password to access protected directories.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 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

19 Experts available now in Live!

Get 1:1 Help Now