Solved

Excel 2010 External links do not update unless source file is open

Posted on 2014-12-29
13
615 Views
Last Modified: 2015-03-03
I have a master workbook with tabs to 12 different spreadsheets.  One tab for each external file.  I have created formulas to pull the info into the master and the data is consolidated.  All of the formulas work and pull the correct information in.  However when I save and close all the spreadsheets and try to only open the consolidated spreadsheet, there is no data.  In order to populate the spreadsheet, I have to open all of the individual spreadsheets.

My consolidated spreadsheet will be linked to others, so I need to have the ability to have all the sheets "chained" together and get to the data.

Under Data - Edit Links - Startup Prompt, I have the "Don't display the alert and update links"
0
Comment
Question by:Briad IS
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Hi

This web link may help you to fix you link problem.

Don't ask whether to update links when I open any workbook, and update links automatically

1- Click the File tab, click Options, and then click the Advanced category.
2- Under When calculating this workbook, clear the Update links to other documents check box. If this check box is clear, the links are automatically updated, and no prompt is displayed.

You may found other solution on this link: Control when external references (links) are updated
0
 

Author Comment

by:Briad IS
Comment Utility
i made the changes, opened all the data source spreadsheets, confirmed the data was showing in the consolidated workbook, Saved and closed.  When I re-opened, I get a quick glance at the data in the consolidated sheet and then all the data is cleared.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Looks like all sheets have to be kept opened to pull the values in your destination sheet. It's doing this to me also.

But if you want to pull the data when some other sheets are closed, you may need a driver connection like ADO. But this will be a VBA excel + an ADO.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
To give you more information:
ADO stands for ActiveX Data Objects, and it is an alternative way of connecting to an Excel, Access database as an example.

ADO-connection.png
You can connect by using this code as an example:
Sub Connect_to_Excel_sheet()
    Dim cn as ADODB.Connection

    Set cn = New ADODB.Connection

    With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\sheet2.xls;" & _
             "Extended Properties=Excel 8.0;"
.Open
    End With
End Sub

Open in new window


This code would create a connection and open the sheet2.xls.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
I nice web site that i use for connection strings is: Microsoft Jet OLE DB 4.0 connection strings



Always helpful for any type of connections.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
What formulas are you using for linking the data?

Some formula functions do not work when the source workbook is closed and will return an error. If your formulas include an IFERROR option then this could be resulting in TRUE and returning the value set in the IFERROR rather than the linked value.

Thanks
Rob H
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Briad IS
Comment Utility
Wilder1626,  I will look at the connections, but I have never had to do that in the past when I have a spreadsheet referencing another workbook.

Rob,
I have formulas that look like the following.

=IF(ISBLANK('\\ZTJ-FILESRV01\Shared_Data\JesseM\Monday Reporting\Blank Weekly\[101cog.xls]Sheet1'!A1),"",'\\ZTJ-FILESRV01\Shared_Data\JesseM\Monday Reporting\Blank Weekly\[101cog.xls]Sheet1'!A1)

I have a tab setup for each  "xxxCOG.XLS" for a total of about 20 different files.  As long as the cog.xls files are open, I can see the values.  I save and close all the workbooks, then open the consolidated file and no values are showing.  There are no errors, just no data.  If I go to DATA--> Open Source, all the data appears.

I have done this hundreds of times without any issues, but for some reason this time it will not work.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Which version of excel are you using? It could be that the newer versions don't like linking to an older xls format file.
0
 

Author Comment

by:Briad IS
Comment Utility
I am using Excel 2010 linking to .xls files (Microsoft Excel 97-2003 Worksheet).  The files are created from a food costing program where the data is exported to Excel format.  The only other format that it can export to is .pdf, so this is my only option.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Open one of the source files and a Save As converting it to 2010 format xlsx. Then close that file and see if the link stays visible. Doing the save as with destination and source files open should change the link but might need to do it manually in the Edit Links window.

Thanks
Rob
0
 

Accepted Solution

by:
Briad IS earned 0 total points
Comment Utility
Due to the mappings, changing the format to the .xlsx did not work.  I ended up writing a macro to take the consolidated file and saving off all the tabs and doing a copy-Paste-Special-Value to remove all formulas and links.  I am then using that file for link to the other files.

Thanks for your suggestions Wilder126, BrianIS and Rob.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

7 Experts available now in Live!

Get 1:1 Help Now