• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

.xlsx opening in 2013 "automatic update of links disabled" - has #REF! in cells but opens fine under 2007

We are using a spreadsheet (not mine) and it is created under Excel 2007.  I am testing Excel 2013 and when I receive the spreadsheet, all of the cells show #REF! and I get the message saying that "automatic update of links disabled."  

When I try to enable them and update, it can't find the .xlsm file that I guess the spreadsheet is referencing???  Doesn't matter if I try to update the links or not, it still won't show the data.

This works under Excel 2007...I get the same message about the "links being disabled" but by default, on 2007, I can see the data.  

Any ideas?
0
vianceadmin
Asked:
vianceadmin
  • 4
  • 3
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
If you're using Excel 2007 and 2013 on different machines, it's likely that the external file is a locally-saved file on the 2007 computer.

Before trying to open the file in 2013 again, change the Trust Center Settings (File -- Options -- Trust Center -- Trust Center Settings -- new window -- External Content) so that the security settings for Data Connections and Workbook Links both are set to "Prompt user..."  

-Glenn
0
 
vianceadminAuthor Commented:
Forgot to mention that.  In the Trust Center settings, they are already set to "prompt user."  One thing to note, in Outlook 2013 when previewing the .xlsx, the data shows up.  It only goes to #REF! when you open the spreadsheet.  It could be a locally saved file on the 2007 computer but then wouldn't any version of Excel (2007, 2010, 2013) have issues opening it?  They open fine with 2007.
0
 
Rob HensonIT & Database AssistantCommented:
What formulas are being used for linking to the external data source? Some functions don't work if the source workbook is closed and when the file opens it re-calculates but can't get the "new" data so gives the error.

Try opening the source data file when you have the file with the links open.

Thanks
Rob H
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
vianceadminAuthor Commented:
When it does open, it looks like it's referencing a .xlsm file (that I believe is part of the .xlsx).  In 2013 Excel, when we try to update the links to see the data, it can't find this file.  I was always under the impression that a .xlsx is sort of like a zip file for excel files.  Again, this works fine under 2007.  Doesn't work under 2010 or 2013 so maybe it's a security thing where something is being removed or the .xlsm isn't able to be accessed or wasn't part of the .xlsx that was emailed to the user.
0
 
Rob HensonIT & Database AssistantCommented:
xlsm and xlsx are different file types, xlsm being macro enabled.

You are right that the new formats are effectively a compressed file with different sections but one of those sections won't be an xlsm within the xlsx file. Anything beyond that basic concept is outside of my scope of knowledge.

Once the file is open, look at the the Data tab and click on the Edit Links button, icon with a short length of chain link. This will list all files from which this file is pulling data. Maybe one of these listed is an xlsm file. When you highlight it in the list, the file path will show below the list pane. Are you able to access the specified file path? If that path is a shared directory to which you don't have access, the file won't be able to update.

However, if you are not able to update the links it should just show the previous values; unless as mentioned before the links are using formulae that need the source file to be open.

Thanks
Rob H
0
 
vianceadminAuthor Commented:
I guess what's confusing is that in Excel 2007, I get the message about "content disabled" but the data displays correctly in the cells.  This spreadsheet has been sent out daily for a while and they have always been able to see the data.  When I look at the source of the data in 2007, it shows two .xlsm files and "Error: Source not found" for both of them.  But if that was the case, how does it display the data?  

In Excel 2013, I see the same thing but (source not found) but the cells show the #REF!.  If I try to update the links or correct the links, it can't find the .xlsm (same ones above).  Just not sure how 2007 is even able to display the data if it can't see the source .xlsm files.  I will say that in the Outlook 2013 preview of the spreadsheet, the data "IS" there so it must be something security related when the spreadsheet is actually opened under the newer version of Excel.
0
 
Rob HensonIT & Database AssistantCommented:
As you say, I would assume that there has been a change in how the data is displayed between versions. I believe with 2007, if it was unable to re-calculate linked data, it would just show previous values; don't know whether this has changed in 2013, looks like it might have done.

However, I will ask again, what formuas are showing the #REF! error? Some formulas don't work when the source document is closed. For example, SUMIFS don't work when linking to a closed file.

Have you looked at the Update Links options in this file?

If you are unable to update links, maybe you do not have the necessary access to the source document folder.

Thanks
Rob H
0
 
vianceadminAuthor Commented:
Appreciate the help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now