Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: how to change link source

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This problem prompted to me for a long time.
    I have several files which are referenced by one file. My problem is that, after i renamed the referenced files as a new set of files, i need to go to "Edit->Links" to change sources(since the referenced file names and path are changed) one by one.
    I have tried to record Macro to change the link sources but it doesn't work. Could anyone help me? Thank in advance.

    Here to Learn
    XJ

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Detailed Description:
    A folder Data-0001 contains file Data-0001-Q1.xls, Data-0001-Q2.xls, Data-0001-Q3.xls, Data-0001-Q4.xls, Data-0001-Summary.xls.
    Q1 through Q4 means quarter 1 through quarter 4. These four files are referenced by Data-0001-Summary.xls.
    What i need to do is to make another copy of folder Data-0002 and change all "0001" to 0002", but the referenced files by Data-0002-Summary are still Data-0001-Q1,..., so i need to open Data-0001-Summary and go to "Edit->Links" to change all the referenced file name Data-0001-Q1,..., to Data-0002-Q2,...,. This action can be easily done manually. But I need to do this 200 times. That means i need folder from Data-0001 to Data-0200.
    How can i use Macro to update the link source?
    Thanks!!!

    XJ

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Andrew,

    this does not answer your actual question as what would the macro be, but can you not create a new folder (not copy the existing one with all its contents) then open all 5 workbooks and "save as" with the new filenames into the new folder ?

    Links will change automatically if the books are open.

    Maybe you can use this as a last resort if nobody can come up with some code for you ?


    :: Pharma Z - Family drugstore ::

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you!!!Chris. I posted this question a few days ago, but nobody answer it. In fact i feel your idea is still a feasible idea. Thank again.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    then open all 5 workbooks and "save as" with the new filenames into the new folder ?
    I should have added, save your data files first, not the summary file. Renaming these will update the links in your summary files - you can literally see it happen before your eyes.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •