?
Solved

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

Posted on 2014-12-29
13
Medium Priority
?
2,150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 33

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
 

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 33

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 33

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 49

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

801 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