Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: How to get current path then do the linking

    I have 2 excel files. One is the source file and the other is the final file. These 2 files reside on different folders. But the 2 folders are under the same mother folder.

    To illustrate:
    FileFolder/SourceFolder/source.xls
    FileFolder/FinalFolder/final.xls

    I will need to link several cells of the final file to the source file. So Simply, i just typed in "=" then went to the source file and pointed to the cell i will be linking with. Done! But the problem is..the FileFolder (including its subfolders and all the files under these subfolders) might be copied to different file locations or path, when i did this..the link is gone.

    How do I get the current path then connect it to the filename of the final file plus the cell(row,column) of the source file where i will be linking with? The constant here are the filename and cell(row,column) of the source file. Seemed easy, I keep on testing but I cant get it right. I was able to place/put the "current path + source file name + sheet name of the source" on 1 cell location in the final file. Then i used "&" to add the cell location.

    Ex. 'C:\FileFolder2\SourceFile\[source.xls]Sheet1'!$A$2

    But I can't get the value. Pls. help. Sorry for the long explanation.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    The only native way to construct a range reference from a string in a formula is by using INDIRECT(). However, INDIRECT() will only work when the other workbook is open; if it is closed then it will return an error.

    Without re-inventing the wheel, you could go to Edit | Links | Update values and browse to the correct file. Or perhaps you could give Jan Karel Pieterse's link fixing utility a try? This shouldn't happen very often, right?

  3. #3
    Join Date
    Oct 2005
    Posts
    119
    I can't consider that option of Edit|Links|Update coz there will be a weekly update on the source file. Thanks for the reply.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    Quote Originally Posted by LimaCharlie View Post
    I can't consider that option of Edit|Links|Update coz there will be a weekly update on the source file. Thanks for the reply.
    Why does that mean that Edit|Links|Update isn't an option?

    I guess we could automate this from the Workbook_Open event handler (ie. a VBA solution), if it's worth the effort? I'm not aware of any reason why this wouldn't be viable - but then again, I've never tried it. I expect that if you download that add-in the code will not be protected, so it would give you a head start on the objects and methods you're going to be needing to work with.

    Or someone else might have a better suggestion!

Posting Permissions

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