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

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.
famiitAsked:
Who is Participating?
 
famiitConnect With a Mentor Author Commented:
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
 
burrcmCommented:
Try marking the file read only before opening.

Chris B
0
 
Robert SchuttSoftware EngineerCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
famiitAuthor Commented:
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
 
famiitAuthor Commented:
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
 
Robert SchuttSoftware EngineerCommented:
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
 
famiitAuthor Commented:
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
 
famiitAuthor Commented:
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
 
Robert SchuttSoftware EngineerCommented:
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
 
Stacy BrownSenior Applications AdministratorCommented:
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
 
famiitAuthor Commented:
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
 
Robert SchuttSoftware EngineerCommented:
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
 
famiitConnect With a Mentor Author Commented:
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
 
Robert SchuttSoftware EngineerCommented:
Nice!
0
 
famiitAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.