Links to closed sheets not updating

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi everyone,

I have some links to cells in closed worksheets that do not seem to update to the value in the source cell unless the source worksheet is open.

When I open my workbook, I get a message telling me that some of the links could not be updated, and I have the option to edit links or continue. Upon editing links, when I enter refresh, nothing changes. The values are still not pulled into the workbook from the closed sheets.

The only way to get values in seems to be to open the source worksheets, which defeats the object of the exercise.

The source worksheets are located on a networked drive, so the network may be part of the problem. I have permissions to read and modify the files.

Please could anyone help me fgure out a possible cause for the problem I'm having?

Thank you.

vcoder
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi Jason,

Thanks for this link, it is really useful.

In my case, I an trying to pull in values from cells that have been assigned names in the closed workbooks. From the information in the link, it appears that this is not possible, and does not return the value in the cell.

Would anyone know a way around this, without using the cell coordinates A1, A2 etc. ?

Thank you.

vcoder
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354

ADVERTISEMENT

No, all I am doing is referring to a cell in a closed workbook. No functions are used at all. The cells in question in the workbook are named. Is the use of named cells prohibited somehow when pulling in data from closed workbooks?

='C:\files\data\ClosedWorkbook.xls'!NamedRange

Is the use of "NamedRange" the reason why the value in the cell is not being pulled into the open workbook, unless ClosedWorkbook.xls is open?

Would this work instead?

='C:\files\data\ClosedWorkbook.xls'!A1

I don't see any reason why the first formula would not pull in the data as desired.

Please could someone help me with this problem?

Thank you.

vcoder
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Is the use of "NamedRange" the reason why the value in the cell is not being pulled into the open workbook, unless ClosedWorkbook.xls is open?

Do the named ranges involve "dynamic named ranges"? That might do it. What's the definition of the named range in the other workbook look like?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,886
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It would seem that there is a problem with the formula being to simple as well, maybe it needs a closed book compatible function to kick it into life.

I've just tested with 2 new workbooks, using
='C:\Documents and Settings\jason\Desktop\[Book1.xlsm]Sheet1'!A1
as the formula in the open book (book2) to pull data from the closed book (book1).
Formula is autofilled from A1 to F10

I closed both books, then opened, edited and saved and re-closed book1.

Next re-opening book2 I was expecting updated values, but they remain unchanged, F9 refresh does nothing, only an action such as F2, enter or equivilant mouse action updates the values, even then it only works for the cell on which the action is performed.

The information in the other threads that I've found, here, and on other forums, which I've quoted above and in another thread led me to believe that these values should update when the sheet recalculates, it would appear however that this is not the case.

Unless anyone knows what I'm missing I'm going to keep looking for solutions.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
It would seem that there is a problem with the formula being to simple as well, maybe it needs a closed book compatible function to kick it into life.

I've just tested with 2 new workbooks, using
='C:\Documents and Settings\jason\Desktop\[Book1.xlsm]Sheet1'!A1
as the formula in the open book (book2) to pull data from the closed book (book1).
Formula is autofilled from A1 to F10

I closed both books, then opened, edited and saved and re-closed book1.

Next re-opening book2 I was expecting updated values, but they remain unchanged, F9 refresh does nothing, only an action such as F2, enter or equivilant mouse action updates the values, even then it only works for the cell on which the action is performed.

I repeated your experiment with the opposite result - formulas from the edited, closed workbook were updated as I would have expected. Excel does have settings for how to treat links (update, don't update, ask, and so on). Maybe these should be checked (in XL2007 on the data tab, under edit links > startup prompt). But I was surprised in my case that the links updated automatically without "asking". XL seems funny in this regard to me. The startup options say "let users decide whether to show the alert or not", but gives no indication how a user might do that!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,886
Office Version
  1. 365
Platform
  1. Windows
Thanks xenou, that was something I missed.

Using the same 2 documents saved from earlier, I've now tried all 3 settings on there, the values have still not updated.

Going to office button > excel options > advanced > When calculating this workbook

There are 2 options that relate to external links, both of which are checked, tried unchecking 1, then the other, then both, each time with all 3 settings on the Data >edit > startup prompt tried, nothing seems to change.

A macro recorder test with subsequent edits shows

Code:
ActiveWorkbook.UpdateLink Name:= _
        "C:\filepath\Book1.xlsm", Type:=xlExcelLinks

in an event module will solve this minor updating problem, needs to be executed for each linked file though, will look at a for each loop or similar later.
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi Jason and Xenou,

Thank you both for your replies.

I've been doing a few tests, and it seems that the problem arises when I 'construct' a filepath from entries in several cells, and use the INDIRECT statement somewhere in the process to access data in a Named cell at the 'built' filepath.

I didn't think that this would be a problem, but as indicated in a url in one of Jason's earlier posts, links to filepaths that have been generated using certain statements do not work on closed workbooks.

Is this it then? Is there no way to link to closed workbooks when the filepath is 'built-up' using entries in a series of cells?

The reason why I am building the file path gradually is because I want to have greater flexibility in the types of files I need to access. Also, displaying the constituents of the filepath make it easier to see what is going on.

Thank you.

vcoder
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,881
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top