Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    28

    Unanswered: Link path change

    Hi, I want to change the path to a file in excel link but I always get an error.
    ='\\mylocation\folder1\folder2\[file001.001]Sheet1'!$A$1

    I want to be able to change the file name and the folder name, I tried to do something like this but I always get an error.

    ='\\mylocation\folder1\folder' & A1 & '2\[file' & B1 & '.' & B1 & ']Sheet1'!$A$1

    I also tried different string manipulations but always get an error or excel won't assume it as a formula.

    I even tried to create a function but it also fails if I call it from the worksheet.

    Any ideas

    Thank you.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    want to be able to change the file name and the folder name, I tried to do something like this but I always get an error.

    ='\\mylocation\folder1\folder' & A1 & '2\[file' & B1 & '.' & B1 & ']Sheet1'!$A$1
    Excel considers this to be a string and not a range reference. There's a worksheet function called INDIRECT() which can be used to convert a string to a range reference. Unfortunately there are two drawbacks to using INDIRECT():
    1. It's a volatile function.
    2. If it's pointing at an external workbook which is closed, it returns an error value.

    Point 2 is the big factor for your question because you are trying to reference an external workbook.



    With INDIRECT() put to one side, your options are either to write (or borrow) some VBA code or to install an add-in which has the required functionality. Some choices are discussed on this thread at Daily Dose Of Excel including:
    • MOREFUNC.XLL which includes the INDIRECT.EXT function
    • Harlan Grove's PULL function
    Hope that helps...

  3. #3
    Join Date
    Dec 2004
    Posts
    28
    Going to take a look to those threads.
    Still working to find a way around.
    Thank you for your input.

Posting Permissions

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