none
Change the Path of Link File RRS feed

  • Question

  • I have a pair of Template Excel Files. The First File Cells have links to the Cells in the Second File. These pair of Excel Files are copied through VB.Net for every month activities in a new folder.

    How do I update the Link file Path in the new folder files? The MS-DOS Convention of "." or ".\" or "..\" is not supported while creating the The Template Files. Is there any other way? Thanks

    Friday, August 14, 2015 1:30 AM

Answers

  • Hi KRakLali,

    We could get current workbook path by using Workbook.Path property, then we could split it or get partial string of it.

    For example:

    Dim f() As String
    f() = Split(ActiveWorkbook.path, "\")
    MsgBox Left(ActiveWorkbook.path, InStrRev(ActiveWorkbook.path, "\"))

    If you want to achieve that though UI, I suggest that you could post it to Excel IT Pro Discussions forum.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 17, 2015 2:08 AM
    Moderator

All replies

  • I do have a Variable that describes the Current Month's File Path.

    Friday, August 14, 2015 1:35 AM
  • Hi KRakLali,

    We could get current workbook path by using Workbook.Path property, then we could split it or get partial string of it.

    For example:

    Dim f() As String
    f() = Split(ActiveWorkbook.path, "\")
    MsgBox Left(ActiveWorkbook.path, InStrRev(ActiveWorkbook.path, "\"))

    If you want to achieve that though UI, I suggest that you could post it to Excel IT Pro Discussions forum.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 17, 2015 2:08 AM
    Moderator
  • How do I update the Link file Path in the new folder files? The MS-DOS Convention of "." or ".\" or "..\" is not supported while creating the The Template Files. Is there any other way? Thanks

    There is no need to update the path, when both files are in the same folder the path is locale!

    Try it manually:

    - Make a new file
    - Write 1 into A1
    - Save as FileB.xlsx
    - Make a new file
    - Write a = into A1, switch to FileB.xlsx, select A1 and press ENTER
      This creates the formula =[FileB.xlsx]Sheet1!$A$1
    - Select cell A1, so you can see the formula in the formula bar
    - Save as FileA.xlsx
    - Close FileB.xlsx
      As you see the formula changes to something similar like ='C:\Users\Killer\Documents\[FileB.xlsx]Sheet1'!$A$1
    - Close FileA.xlsx
    - Copy both files into C:\temp

      For testing purposes:

    - Open FileB.xlsx
    - Write a 2 into A1
    - Save FileB.xlsx
    - Close FileB.xlsx

    - Open FileA.xlsx
      As you see the formula has changed to the local path ='C:\Temp\[FileB.xlsx]Sheet1'!$A$1
    - When you get a security message, acivate hyperlinks, resp. accept to update links
      As you see the value of cell A1 changes to 2

    An issue in your code might be that the files are opened and you perform a SaveAs. In that case the path resp. link points to the original file.

    Andreas.

    Monday, August 17, 2015 10:05 AM
  • Thanks for all the support
    Sunday, August 30, 2015 4:35 PM