Restablish Links after converted to addresses

[Dr Dew 2]

Here's a challenge to the Excel Guru's out there.

I have a workbook that is linked to many other books. Each week the path of the link changes to a different folder. I've parsed the path into several cells and only need to change a single cell with the new date and all of the parsed cells change according to the new date.

My problem is, after remerging the path into the destination cell(s), I'm trying reactivate that cell as a Link. So far, I've copied and pasted the values in the cell to change it from a formula to an address. Then when I select the cell (Edit Mode) and just hit Enter, it will become an active link again. Problem is, I have over 10,000 cells that need to be reactivated. This is a weekly report so this process is done regularly. Any ideas or suggestions????

Thanks for your expertise.

 

[Dr Dew]

Thanks for the views from others. I resolved the issue by pasting the text string into the cells and then I used the Sendkeys method to cycle through all of the cells with a [F2] and [Enter] through the range. It works like a charm in case anyone else has a similar requirement or need.

 

[xenou]

You can also do the same without VBA by doing a find/replace on the equal sign, which has the same effect as F2 + Enter:
Find: =
Replace With: =

I've yet to diagnose the precise cause - you'd like to think this wouldn't be necessary.

 

[Dr Dew 2]

Thanks Xenou,
We used that method whrn we had only about 350 links, but now we have between 11,000 and 33,000 links to update and the VBA method works for us better and with fewer user interventions.

 

[xenou]

Thanks for the feedback. This is a common problem, so if you'd like to share your solution in more detail I believe it could be of help to others. Glad you're on track now.

Cheers,
ξ