Hi all,

I have 2 excel workbooks that reside on a network drive. One is a file (File A) that users interact with, the second (File B) is a file that I keep a whole bunch of settings in that controls the behaviour of File A, so File A has many cells that link to File B. If I physically open File B (the settings file), and then open File A I can see that all the linked cells get updated properly (and the actual links go from showing the network address to just the file name).

However, I then have a third file with some VBA in, I use this file to automatically open both File A and File B. if I do that then the links in File A do not automatically update, even with both files open, and if I look at the links, they still show the network path rather than just the file name, again despite the linked file being open. If I go to File A and open the connections, and click Update, it errors saying "Source not found". At that point, if I close both files and reopen them manually it all works again. So what's the difference between doing this in VBA, which doesn't update links, and doing it outside of VBA, which does?

I have tried doing Workbook.UpdateLink, which gets a 1004 error. I maybe thought that the files are being opened in separate instances of Excel but looking on the net the code I'm using to open the files suggests it's now (see below). So any ideas of what on earth is going on? I am using Excel 2010, and something else that may be useful is that the network location is also a mapped drive, not sure if that could be throwing things off somehow.

Dim TWb As Workbook, Wb As Workbook

Set TWb = Workbooks.Open(generatePath)
Set Wb = Workbooks.Open(settingsPath)
Any help is much appreciated!

Chris