Forum Discussion

reditguy's avatar
reditguy
Iron Contributor
Jan 17, 2019

Excel linking

Hey all,

 

We are in the middle of a project which will involve moving our Excel files to a different location, and I would like to know how Excel handles links.

 

For example, this is our current folder format:

 

H:\excel\file1.xlsx

H:\excel\file2.xlsx

H:\excel\folder1\file3.xlsx

H:\default\file4.xlsx

etc.

Some of those files link to the other files

 

This is how we will eventually have it

h:\common\excel\file1.xlsx

h:\common\excel\file2.xlsx

h:\common\excel\folder1\file3.xlsx

h:\common\default\file4.xlsx

 

So basically moving all the folders and files that are currently under H:, to H:\common

 

Will excel automatically know that the files were moved and update the links?

 

What is the best way to handle this?

 

 

 

 

 

Excel
office 365
  • Nauthstar's avatar
    Nauthstar
    Iron Contributor
    Jan 18, 2019

    Hi,

    There are several methods files can be linked and it's likely you may get some issues regardless of how you try to update them.

    • Links can be  'hard-coded' in Formulas
    • Hard-coded through VBA
    • Through the use of Power Pivot

    If you know you only have formula based links then use the built in 'Edit Links' option.  (see attached picture)  open your file, select the DATA tab and select Edit Links then select the link and Change Source... if the option is greyed out then there are no formula links between files.

    Alternatively, if you only have a few files, you could try the following steps...

    1. Create the new directory "h:\common\excel\"
    2. Open ALL you existing Excel Files from their current locations
    3. Save each one to the New directory in turn, but don't close the files.
    4. Once  ALL files have been saved to their new locations, then close them down.

    This should take care of hard-coded formulas. Moving linked files 'normally' updates the formulas when both the donor and recipient files are open.

     

    You may also like to try Microsoft's Add-in tool called "Inquire", to list the links your file has with the outside world.

     

    If your links are via Macros or VIB, you will probably have to update them manually.  

    In future, try to set your VBA code so it opens and saves along the lines of...

    ActiveWorkbook.Path & "\" & "\" & myFile.xlsx

    That way, it won't matter what the folder is the file is in.

     

    If the links have been created using Power Pivot, unfortunately you'll need to manually update them 

     

    One final point,  I see you are increasing the number of characters in your file path.  Be aware that there is a limit to the number of characters you can have.  Once reached, the links may not work.

     

    Kind regards 

     

    • NikSat's avatar
      NikSat
      Copper Contributor
      Dec 27, 2023
      Hi Nauthstar ,

      I have a similar problem with this. I am using PowerPivot, so I have to update the links, which is fine manually, but the question I have is whether the formulas I put in my data model of power query will be affected. Do I need to work on them again?
      • Nauthstar's avatar
        Nauthstar
        Iron Contributor
        Jan 03, 2024

        NikSat 

        I would try to test this out yourself, as each case could be slightly different, based on the way user set up.

         

        The way I'd go about is to go to my source file (assume EXCEL) and change one of the entries to 99999999 - something large.  Then do your other changes and see if it pulls through to Power Query correctly.  If so, change the 9999999 back, otherwise, investigate further.