Links to closed sheets not updating

[vbacoder]

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

 

[jasonb75]

Similar issue here, see if any of the info posted helps

http://www.mrexcel.com/forum/showthread.php?t=486783

 

[vbacoder]

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

 

[jasonb75]

Are you using funtions with the ranges?

http://excel.tips.net/Pages/T003130_Using_Named_Formulas_Across_Workbooks.html

The way that reads, it should work, but it depends on the functions in use with the formula.

 

[vbacoder]

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]

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]

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]

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]

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

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]

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