Changing source file using paste link

[blivy]

Hi,

I feel like there should be a really easy answer to this but I just can't work out how to do it!

I've used the paste link function to copy numbers across from excel to word. I've since saved a new copy of my excel document but the word document is still linked to the previous excel document. Therefore I'm trying to update the source file from exceldoc v1 to excel doc v2.

I can see that within the 'Links' window there's the option to change source and I can change the source for each link individually but not all of them at the same time. If I select all my links and click 'change source' I have to select the excel doc v2 again and again, once for each link. I have hundreds of links so can't do this individually for every one. Surely there's a workaround for this?

Thanks!

[6StringJazzer]

I have tried to do this in the past and have found no way to directly edit existing links in bulk without having to re-paste them all.

I have had success with this method:

1. Make sure the Word file and both Excel files are closed.
2. Rename the original Excel file to something else.
3. Rename the new Excel file to the original name (the one used in the Word links).
4. Open the new Excel file.
5. Open the Word file. The Word file should now show data from the new Excel file.
6. While the Word file is still open, save the new Excel file using the new name.
7. The links in the Word file should now reflect the new Excel file name.

Please let us know if that works for you.

[blivy]

I have tried to do this in the past and have found no way to directly edit existing links in bulk without having to re-paste them all.

I have had success with this method:

1. Make sure the Word file and both Excel files are closed.
2. Rename the original Excel file to something else.
3. Rename the new Excel file to the original name (the one used in the Word links).
4. Open the new Excel file.
5. Open the Word file. The Word file should now show data from the new Excel file.
6. While the Word file is still open, save the new Excel file using the new name.
7. The links in the Word file should now reflect the new Excel file name.

Please let us know if that works for you.

That did work thanks.

The trouble is, I need to be able to move the word and excel documents to a different file. However, if I move the excel and word document into a different file the links all break. Is there a way around this?

[blivy]

Anyone?

If it's not possible to edit existing links in bulk, is it possible to set them up so it works as long as they're both in the same place? I've been developing a linked excel and word model for others to use but as soon as I send it to anyone the links all break.

[6StringJazzer]

Do you mean that you need to be able to move them to a different folder? I have done that with no problem. If both Excel files and Word files are in the same folder, then you close the files, and copy or move them to another folder, everything should still work. The link addresses are relative, although if you email it to someone and they open it directly from the email instead of saving it first, everything breaks.

[blivy]

Do you mean that you need to be able to move them to a different folder? I have done that with no problem. If both Excel files and Word files are in the same folder, then you close the files, and copy or move them to another folder, everything should still work. The link addresses are relative, although if you email it to someone and they open it directly from the email instead of saving it first, everything breaks.

Ah thank you! Not sure why it didn't work the first couple of times I tried it, although I perhaps because my macros weren't working correctly and when I closed it I hadn't de-bugged them.

[blivy]

Do you mean that you need to be able to move them to a different folder? I have done that with no problem. If both Excel files and Word files are in the same folder, then you close the files, and copy or move them to another folder, everything should still work. The link addresses are relative, although if you email it to someone and they open it directly from the email instead of saving it first, everything breaks.

I'm actually still having a few problems with this. As far as I can tell on further inspection, so long as you move (cut and paste) both documents to a new folder in the same drive then the links will update automatically. However, I'm trying to place it on a shared drive so everyone can use it. However, when I cut and paste the links don't update, they remain pointing to where the file used to be on my C: drive.

It's not essential that I solve this problem as I'm quite happy to just to manually update all the links myself (it will just take a bit of time). However, I want people to be able to use the linked word/excel file and copy them into there own documents. So ideally they would copy both files to a different location and the word document they are working in would be linked to their version of the excel document. However, the two issues with this are:

1. I can only get the links to automatically update where the file is cut and pasted rather than copy and pasted (I can't have everyone using the same document - they need their own separate versions); and
2. Even when I cut and paste (rather than copy and paste) between drives, the links remain pointing to the old drive.

I've struggled with this quite a lot and have potentially got a work around for issue 2. The users would just have to copy and paste the files into a different folder in the same shared drive rather than into their documents - I'm fine with this. However, this doesn't solve the issue whereby the links only update if you cut and paste rather than copy and paste.

I wonder whether the links in my document aren't actually relative, as the full file location is listed for each of the links. In which case have I set these up correctly?

Otherwise I'm kind of back to square one, whereby the only solution is for the user to manually update the location of all the links every time they create a copy, but excel doesn't allow anyone to update links in bulk, as far as I can tell.

I hope this isn't a fundamental issue that renders the hours and hours of time I've spent preparing these documents wasted!