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

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"
LVL 1
Briad ISDirector, ISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wilder1626Commented:
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
Briad ISDirector, ISAuthor Commented:
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
Wilder1626Commented:
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
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Wilder1626Commented:
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
Wilder1626Commented:
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
Rob HensonFinance AnalystCommented:
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
Briad ISDirector, ISAuthor Commented:
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
Rob HensonFinance AnalystCommented:
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
Briad ISDirector, ISAuthor Commented:
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
Rob HensonFinance AnalystCommented:
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
Briad ISDirector, ISAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.