Update automatically link path excel sheet

I work on several Excel files, I have a main spreadsheet and other small spreadsheets where i made a lots of cells links to the main spreadsheet , However after i download all files with the main spreadsheet to new computer, the cell path do not update automatically and keep show C:\Users\old computer name\Desktop.... , I have to go to data>update links for each file to new computer name.

An other comment, what I don't understand is the fact that the main spreadsheet update automatically because some cells in this main sheet are also linked to the small sheet

thank you very much

Internally, within a workbook, Excel is very clever and if you change something, move something, delete something, Excel adjusts all the cell references because it is holding everything to do with the active workbook in memory.
But Excel is not able to adjust external file references when you move linked files around on your computer. And it certainly cannot know if you have shifted the files to a different computer.
Excel is a very well behaved programme - it does exactly what you tell it to do. And it is not a mind reader. So it will continue to look for the files in the folder you told it to look in, and if that happens to be a folder named old computer name, then it will try (without success) to look for them in a folder of that name on its own C drive.

Can anything be done?
If you mirrored your old folder structure on your new PC, then it should be possible to write a VBA routine to replace the text in the cells so that the workbooks can be "linked" on the new PC.
So if for example
C:\Users\old computer name\Desktop\Excel\myfile.xlsx
can be replaced with a consistent (new) folder structure ie something like
C:\folderA\folderB\folderC\myfile.xlsx
where where A, B and C do NOT change, then you are fortunate.

If you have put files in different places - then fixing the links is a tedious and MANUAL job. And you learn to not make the same mistake next time!

Some files may be in the default folder, and (totally by chance) your main spreadsheet is linking to them - but I would not rely on that.

If you still have your old computer, or a copy of the relevant folders on the C drive, then you may be able to mirror your old structure on your new machine to get your links again.

Hope this explains what has gone wrong.

thank you for your answer, i understand exactly what you mean but I hoped that some solution could be possible especially after I found that the main sheet update automatically.

anyway, thanks a lot

You may still be able to do something at an individual file level.

If workbook myFile.xlsx which was linked to fileA.xlsx and fileB.xlsx that were previously in
folder C:\Users\old computer name\Desktop\folderX
and now they are in C:\documents\folderY

then could write a routine using VBA to amend all the formula in myFile.xlsx and replace
C:\Users\old computer name\Desktop\folderX\fileA.xlsx
with C:\documents\folderY\fileA.xlsx AND
C:\Users\old computer name\Desktop\folderX\fileB.xlsx
with C:\documents\folderY\fileB.xlsx

In fact, the VBA could be written in such a way that you could input the old file link path and its equivalent new link path for all the linked files. But you need to manually arrive at the list of old and new link paths first.

This macro
- changes all the links to ONE other file from a workbook for you very quickly
- if there are several different files being linked to, run macro separately for each one

Before running it you will need to know:
- the name of the link-to file and its extension
- the path on your old computer(copy from one of cells in Main spreadsheet)
- the path on your new computer

Amend these 2 lines, to the beginning bit of your old and new paths
- oldPath = "C:\Users\old computer name\Desktop\"
- newPath = "C:\NewFolder\NewSubFolder\etc"
(they will make the input easier if there are several different file paths to update)

The macro contains lots of checks to make sure that you are inputting everything correctly and the sub will exit if it cannot find your file in your new folder avoiding a lot of potential problems.

IMPORTANT Run this vba from a copy of any file where you need to update the links
(if you make a mistake, or the routine does something you do not expect, you will need the original file)

To run the macro
- copy the VBA into a module in (a COPY of) the Main Spreadsheet file
- Run it
- it will ask for filename, old path and new path (it is not case sensitive)
- and your links will be updated
- check them to see all is ok!

Although the vba looks long because of all the checks etc, in essence everything is carried out by one line
- Cells.Replace What:=replaceThisString, Replacement:=withThisString, MatchCase:=False

Please Login or Register  to view this content.

Thank you for this solution, I m not too good with macro but I will definitely try it. Thank you a lot

There is an Excel solution which should also work -

On home tab, on right, under "Find and Select", take option "Replace"

- Find What = old path including full filename
- Replace With = new path including full filename

which does the same thing as this line in the macro

Please Login or Register  to view this content.