External Links in VBA

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.

Please Login or Register  to view this content.

Any help is much appreciated!

Chris

Maybe you try open files using "GetObject" command ? Variable declared as Object ...

I've just tried using GetObject and the same result. What I have been able to determine though that the drive being mapped is possibly the problem. When I have both workbooks open, and I link cells all you see is [File A]Sheet1!A1, for example. And when you close the file that then turns into the full file paths, i.e. [H:\Documents\Test\File A.xlsx]Sheet1!A1. However, if I then manually edit that link address to the true server address (i.e. not the mapped drive) [\\SET-01\Staff$\Documents\Test\File A.xlsx]Sheet1!A1, magically it does work all of a sudden. It just makes no sense that the behaviour of opening 2 files manually should be different to opening 2 files via VBA.

And what if, change the procedure of opening files to the usual "Workbooks.Open" and apply additional parameters in it?

Please Login or Register  to view this content.

You can also check what happens if you open this files with the script - I have found one on sites of TechNet MS - "vb script - to update all links then all links to break". It can be rewritten and shortened (see enclosure)

Sorry for bad english

Ok problem solved, turns out the file path I was using to open the files affects the link paths themselves. So if I use the mapped drive file path to open the file inside VBA, the linked cells also use the mapped paths, and this then breaks the links because for whatever reason, Excel now can't see the mapped drive. If I open the file in VBA with the full server path, the linked cells now use the server path and the links are then free to update. So there you go, problem solved, but no idea why this behaviour is happening!