Maintain link from Word to Excel even when Excel file name changes

Thanks for looking into this for me. I definitely appreciate it.

I've updated the title as per your suggestion.

What type of target is your link in Word:
Excel paste link.png

If I right click the Excel box in MS Word I see 'Linked Macro-Enabled Worksheet Object'

Is that what you are asking?

Pretty much. I was wondering how you created the link, because there are several ways to link.

The Word macro recorder captures the fact that you are pasting from Excel, but it does not capture the name of the file or location in the file. That information has to be stored by Word but the VBA code merely knows that it is pasting from the clipboard. So there are two approaches. One is to dig into the bowels of Word and see if it's possible to use VBA to modify the name of the linked Excel file in an existing link. The other is to use VBA to create the link from scratch. I have a pretty good idea how to do the second and no clue about the first.

So I'll be back if I can sort some of this out.

Thanks.

I am open to any solution that meets my needs. I had done a Google search which is how I found the initial linking method I am using, but I am by no means tied to it. This is new functionality I am trying to implement to make my team's life easier and am still just playing around with it. If there's a better path to my desired end state, I am more than happy to adopt.

Hi 6StringJazzer,

I was wondering if you had come up with any suggestions to solve my issue?

Thanks
Bill

Interestingly, since your post I ended up having exactly the same issue--I have a Word proposal template that I want to link to Excel price buildup, and have not yet figured out how to keep or re-establish the links when I make copies of the two files and put them in a different folder. I'll let you know if I work it out.

Great! Nothing like personal investment to help get things moving alone.

I look forward to seeing what you figure out.