Update workbook links without opening other workbook

[Justair07]

Hello everyone,

I have two workbooks. One is solely used for storing raw data (much like the backend of a db). The other workbook is a dashboard. It's linked to the first workbook. I have several issues:

The dashboard won't update unless source data workbook is open. My users need to be able to open and close the dashboard at will. Asking them to open the source file is not an option because it's a large file and takes a long time to open.

I know there are best practices when doing something like this and I was hoping someone could share with me.

Thank you,

-Justair

[john55]

Hi,
you find a lot of solutions for copying or sending data to an external file without opening/closing that file if you are looking for something like that.

[newdoverman]

Taken from https://support.office.com
Manually update only some of the links to other workbooks

1. Close all workbooks.
2. Open the workbook that contains the links. (This is the destination file and not the source file)
3. On the Data tab, in the Manage Connections group, click Edit Links.
4. In the Source list, click the linked object that you want to update. ...
5. Click Update Values.

[Justair07]

Hi John,

Can you share some of these solutions?

Thank you.

[john55]

the solution provided by newdoverman seems to be better but it's your choice...
see if this one helps you! change the file name/path/range to your suits.

Please Login or Register  to view this content.

[Justair07]

Hi John,

Yes newdoverman did provide a good solution for only updating the data when I manually want to. This is perfect since the data is only updated once a week on Monday morning and when I do update the destination file it takes about five minutes. The issue that still remains is that some of the formulas are returning a #VALUE if the source file is not open.

Work arounds or solutions?

Thank you!

[newdoverman]

Do a little investigation of the source file.
Go to the cell(s) that are resulting in errors and examine them for "oddities" in the values of those cells. It will likely be easier to correct mistakes in the source data than to try and have code written to take care of all "odd" possibilities.

[Justair07]

Thank you. I believe I fixed the issue.

Do you know if Excel updates links when a workbook is saved? I'd prefer it not if it does. That way the workbook wont take 5 minutes to save every time someone does something in it.

[newdoverman]

I have only encountered a prompt to update links on opening.