Excel VBA change (or edit ) links Macro

[DoubleHubble]

Excel VBA change (or "edit") links Macro

I have a Excel spreadsheet that has several links in it which I need to change to a different computer/user. The links are all the same and to change the source I have to go to "Edit Links" and manually change the location to the specific location on my computer.

For example my link is: 'C:\Users\My Name\AppData\Roaming\Microsoft\AddIns'

What is the quickest way to change/automate this link upon opening this file from a different user's directory (to say: 'C:\Users\Any Name\AppData\Roaming\Microsoft\AddIns')?

So not only do I need to change the link, but I need to change it to the current user's name -- which could be anyone.

To summarize I want the cells' values to change from:
='C:\Users\My Name\AppData\Roaming\Microsoft\AddIns\Addin.xla'!ADDFUNCTION(S3,$B$2)

to:
='C:\Users\Any Name\AppData\Roaming\Microsoft\AddIns\Addin.xla'!ADDFUNCTION(S3,$B$2)

without the user (Any Name) having to do anything -- ADDFUNCTION is a simple/dummy addin function that I made to add two cells together for purposes of answering this question.

 

[Pythagorean]

I don't know excel that good (i use MATLAB for excel-type stuff) but I'll give two suggestions in hopes that Excel can perform at least one of them:

1. relative directory. If Excel allows, instead of doing the absolute directory, C:\stuff\mydir you can reference the relative directory to the application's "local" directory: .\mydir

2. if statement and a computer function. If Excel has a function that reports some identity about the competer, you could run that test and choose the directory based on the computer identity.

The syntax and terminology might not be the same in Excel, but hopefully you can find something in help or google.

 

[Redbelly98]

You can try this. Cell D5 will be the text string that you want, DoubleHubble.

In cell A5: C:\Users\My Name\AppData\Roaming\Microsoft\AddIns\Addin.xla'!A DDFUNCTION(S3,$B$2)

Cell B5: =LEFT(A5,9)

Cell C4: 200 [or some integer larger than the largest text string you expect to have]
Cell C5: =MID(A5,17,C$4)

Cell D4: Any Name
Cell D5: =CONCATENATE(B5,D$4,C5)