Solved

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

Posted on 2014-12-29
13
739 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
ID: 40523335
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
ID: 40523454
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
ID: 40523511
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
ID: 40523515
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
ID: 40523549
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 32

Expert Comment

by:Rob Henson
ID: 40533293
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Briad IS
ID: 40550274
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 32

Expert Comment

by:Rob Henson
ID: 40550730
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
ID: 40552641
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 32

Expert Comment

by:Rob Henson
ID: 40553458
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
ID: 40586060
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 46

Expert Comment

by:Martin Liss
ID: 40641536
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

932 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

12 Experts available now in Live!

Get 1:1 Help Now