VBA to Change All Links Within a Workbook Based on Drop Down Selection

[davidiain91]

Greetings Excel Users,

My company use the box.com desktop system to store files, essentially meaning that every file on the box has a path:
C:\Users\User1\Box Sync\Folder\File.xlsm

I have a consolidation Worksheet which automatically updates using data from other worksheets.

Now, this Worksheet works perfectly on my computer, but on any other computer in the network the links break because when another user opens the workbook the links remain as:
C:\Users\User1\Box Sync\Folder\File.xlsm

instead of:
C:\Users\User2\Box Sync\Folder\File.xlsm

My workaround solution for this problem was to write a macro which upon the user selecting their name from a dropdown list, a macro activated which used find and replace to edit the cells.

Unfortunately this appears to not work for links and the links are resolutely refusing to change.

Any help in this matter would be greatly appreciated.

Thank you.

David

[natefarm]

A file shared by others on the network shouldn't be stored on the C drive anyway. It needs to be in a common network location where everyone can get to it. You can resolve it by first opening all the workbooks. Then in each data source workbook, do a Save As to a network location. Any links to those workbooks will adjust to the new location.