Linked Excel Files

Hi everyone!
Have issues with copy the linked xls files...

I have a folder of abt. 100 files and there are many links between them.
When i copy the entire folder to another location, new copied files are still linked to the files in the source folder.
Do you have any idea what might cause that? and how can i get through it?

Thanks for the help in advance!

Hi tatiana_

Welcome to the forum.

There are 2 possible solutions that I know.

Just to be clear, I call the source file the file from which the data comes; the target file is the file to which the data goes.

1. Open your target file. Open your source file from the old location. "Save as" to your new location. This will update the link.

2. Open your target file. Go to Data > edit links >change source. This will enable you to point to the correct file in the new location.

Hope this helps

Regards
Alastair

aydeegee... you are correct.


MaSS

Originally Posted by aydeegee

Hi tatiana_

Welcome to the forum.

There are 2 possible solutions that I know.

Just to be clear, I call the source file the file from which the data comes; the target file is the file to which the data goes.

1. Open your target file. Open your source file from the old location. "Save as" to your new location. This will update the link.

2. Open your target file. Go to Data > edit links >change source. This will enable you to point to the correct file in the new location.

Hope this helps

Regards
Alastair

Thanks for the help!But its a huge number of files and it will take lots of time to do save as and change sources for each file.
I have many monthly reports and every month i just copy the entire folder ( with abt 10-15 files) and all links change accordingly.
But this time its a folder with more than 100 files and when i copy the whole folder, links do not update accordingly - it remains with addresses to the old folder in the old location.

Have been struggling with this issue for quite some time now

Hey there,

You can create macros to automatically remove the hyperlink.

1. Press Alt + F11 to start the Visual Basic Editor.

2. Double click the workbook you are using on the Project Explorer and type the following code:

Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

3.Save your work.

4. Run the macro by pressing Alt + F8 or using the menu by View > Macros

5. Select the macro you have made, it should have the name ‘RemoveHyperlinks’.

I hope this will help..

efo,
Jolicharts.com

Hi tatiana_

Ae you happy with the answers or do you need further assistance?

Regards
Alastair

Hi Tanya

I am confused.

I have set up over 100 files (101, actually) which all link to another. They are all in the same directory. Using Windows Explorer I have copied them all to a new directory and the link correctly shows the new directory. (Also it does not make sense to me that the link will update correctly on a few files, but not on many.)

Anyway, I have prepared a macro that will move all files and put them in another directory, correctly updating the link. There is a Control file that should be located somewhere other than the directory that is being transferred. I have only tested this with a link to one file - but as long as the linked files are all in the same directory it will work.

Let me know how your get on.

Regards
Alastair

Originally Posted by aydeegee

Hi Tanya

I am confused.

I have set up over 100 files (101, actually) which all link to another. They are all in the same directory. Using Windows Explorer I have copied them all to a new directory and the link correctly shows the new directory. (Also it does not make sense to me that the link will update correctly on a few files, but not on many.)

Anyway, I have prepared a macro that will move all files and put them in another directory, correctly updating the link. There is a Control file that should be located somewhere other than the directory that is being transferred. I have only tested this with a link to one file - but as long as the linked files are all in the same directory it will work.

Let me know how your get on.

Regards
Alastair

Thank you so very much Alastair! =)
i also wondered why the links were not getting updated while coping the whole directory...because i do the same every month with another directory and it works properly. I suppose that the issue is due to the size of the "unlucky" directory.
I will try your macro on Monday and will let you know how lucky i am =)
Thanks a ton once again!
Have a great weekend!=)
Tanya