Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Excel VBA change (or edit ) links Macro

  1. Jan 17, 2012 #1
    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.
     
  2. jcsd
  3. Jan 17, 2012 #2

    Pythagorean

    User Avatar
    Gold Member

    Re: Excel VBA change (or "edit") links Macro

    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.
     
  4. Jan 17, 2012 #3

    Redbelly98

    User Avatar
    Staff Emeritus
    Science Advisor
    Homework Helper

    Re: Excel VBA change (or "edit") links Macro

    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)
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook