?
Solved

Excel 2010 file with links - how do we open without updating?

Posted on 2012-09-06
15
Medium Priority
?
2,183 Views
Last Modified: 2012-09-23
We've recently updated our computers to MSO 2010.  Two users have reported that excel spreadsheets that used to have data, now have #ref$ errors.  In looking at these particular excel files, I have gone to the Data tab, Connections section and looked at Edit Links.   There I find that there are references to two other files which we do not, and have never had.  One reference is to an xlsm file and one is to an xls file - but as I said we've never had these files.

The two users report that previously when they received the excel sheet (in either MSO 2003 or MSO 2007), there was data present in all of the cells which now have errors.  The file comes from a vendor who is providing the spreadsheet, but has never providing the two other files I see referenced under Edit Links.  I have Excel 2010 so cannot confirm how it did or did not work before.  I just know that now the users cannot see data, they just get the #ref$ error -- why were they not getting this error with prior versions of Excel.

I have gone into my Excel Settings - File - Options - Trust Center - Trust Center Settings Button - and the External Content tab.  Here I have selected "Disable all data connections" and "Disable automatic update of Workbook Links."   Then I've attempted to open the excel file and still get the same error.

Any thoughts on how/why this used to work without us having the other two referenced files and how we can make it work again in Excel 2010.
0
Comment
Question by:famiit
15 Comments
 
LVL 28

Expert Comment

by:burrcm
ID: 38375490
Try marking the file read only before opening.

Chris B
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38375690
It's been a while but I think I solved a similar problem with VBA code like this:
Sub BreakLinks()
    Dim wkb As Workbook
    Set wkb = Workbooks.Open("c:\temp\test.xlsx", False) ' the 'False' means: do not update links
    Dim lnk
    For Each lnk In wkb.LinkSources(xlLinkTypeExcelLinks)
        wkb.BreakLink lnk, xlLinkTypeExcelLinks
    Next
    'wkb.Save
End Sub

Open in new window

0
 

Author Comment

by:famiit
ID: 38375961
Chris B - I tried marking the file as Read Only - and it still opens with everything as errors.

Robert - are you suggesting that I open the file in Excel and create a macro with the VBA code you have posted and run it?  With the file open I have gone to the Data Tab - Connections - Edit Links and I have selected Break Links there, but everything is still error.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:famiit
ID: 38375981
Another interesting point - The original excel spreadsheet was received in an email.  We use Lotus Notes for our email.  In Lotus Notes, when we double-click on attachments we can select [Open] which launches excel or we can select [View].  Using [Open] we see the file with errors in Excel.  Using [View], the spreadsheet opens in a "File Viewer" tab in Lotus Notes and I see the data!  The spreadsheet in File Viewer doesn't look as good as Excel, but at least the errors are gone and the data is there.  So it seems I should be able to somehow view this in Excel...
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38376064
You would create the macro in a separate workbook, otherwise you'd probably get an error. I created a test workbook and the macro did break the link for me, so I have hopes this should work for you. Maybe also first uncheck any options regarding automatic updating of links. By the way, when I first opened a workbook with links I got a warning about automatic updating being disabled so maybe that's the easiest way out, ramp up security and it won't update by default?

Any idea if the Lotus excel viewer is a program of their own? Maybe an alternative is to use the excel viewer Microsoft offers: http://www.microsoft.com/en-us/download/details.aspx?id=10
0
 

Author Comment

by:famiit
ID: 38376176
Robert, I'll try your macro in a separate workbook.  I did change security in the following way: In Excel Settings - File - Options - Trust Center - Trust Center Settings Button - and the External Content tab.:  Here I have selected "Disable all data connections" and "Disable automatic update of Workbook Links."   Then I've attempted to open the excel file and still get the same error.  Is this where you meant to change settings?
0
 

Author Comment

by:famiit
ID: 38376205
Robert, I did download and use the Excel viewer adn there I CAN see all the data, no errors.  I would consider that a work-around, but ideally would like to open the workbook in Excel 2010 itself so that users don't have to go to the extra effort to open the viewer.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38376374
Yes, those are the settings I meant but also (I use Office 2007 but maybe it's the same) there's a setting under 'Advanced - General', maybe that will allow users to prevent the update on open.
capture optionAlthough, after reading this I think there's a chance it's not going to be possible at all to open without updating because you're on a newer version of Excel than the version used to save the file. What you would be left with is using an older Excel (hope you have one left somewhere) and break the links with that...
0
 
LVL 14

Expert Comment

by:Stacy Brown
ID: 38377542
I found the XLStylesTool for Excel 2010 and 2007 to be a great resource for files with these ref errors.  I'm not sure if it will fix your specific problem, but you could try it against a copy of the file you are experiencing problems with, especially if robert_shutt's suggestion above doesn't work for you.

I tried to attach the .zip file for you but since it contained a .exe it wouldn't allow the attachment.   You can also find tons of info on this here:  http://www.copernici.com/Pages/XLStylesTool.aspx  and here: http://sergeig888.wordpress.com/2009/10/13/sharing-useful-utilities/

I suggest you read the README in the ZIP before doing anything and that you run it on a COPY of your Workbook first.
0
 

Author Comment

by:famiit
ID: 38383265
We have several files going back over some years for which we are now getting an error.  I am hoping not to have to edit these old files - and as I've said before, I don't have the original spreadsheets that are being linked to.  My hope was that there was some setting within Excel 2010 that would prevent the recalc...
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38384407
If you didn't find anything useful in the previous 2 posts there may not be a straightforward way.

It may be possible to copy just the data if it can be found within the xlsx archive or a little 'conversion' program could be written using an alternative way to read the file (read data through ADO and update all cells with errors in it, may be an option). I have already tested with an Excel 2007 file and ACE driver that the data read is the old data and not an error (nor the new data). Not exactly your situation but I'm hopeful it could be made to work for you. Then process all files in a certain directory for example and you could be done...
0
 

Assisted Solution

by:famiit
famiit earned 0 total points
ID: 38394449
I've found a work-around for the files with #REF$ errors with links to other spreadsheets we do not have:

Open the file, if you see errors, do the following:
* Select File, Options
* Select the Formula tab from the left
* Under "Calculation options", for "Workbook Calculation" select "Manual"
* Select the [OK] button in the lower right corner of the window
* Exit the spreadsheet but do not exit excel and do not save the file
* Reopen the file and the errors are gone.

So far this has dependably opened all files that were giving the #REF$ error.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38394719
Nice!
0
 

Accepted Solution

by:
famiit earned 0 total points
ID: 38396935
I found an instance where this didn't work and we had to first have Excel open to a blank (or other) spreadsheet and turn the Workbook Calculation to Manual and then open the file generating the error.

Open the file, if you see errors, do the following:
*Exit Excel but do not save the file
*Start Excel again with just a blank workbook
*Select File, Options
*Select the Formula tab from the left
*Under "Calculation options" (as seen below), for "Workbook Calculation" select "Manual"
*Select the [OK] button in the lower right corner of the window
*Reopen the file
0
 

Author Closing Comment

by:famiit
ID: 38426051
Several suggestions involved modifying the original spreadsheet (for which we did not have the linked data), and some suggestions involved a process to extract the data.  This wasn't desireable since we had so many other files like this one.  So the work-around to first open Excel with Manual workbook calculation was the best fit.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 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