Restablish Links after converted to addresses

Dr Dew 2

New Member
Joined
Jan 13, 2010
Messages
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Dr Dew

New Member
Joined
Jan 13, 2010
Messages
2
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Jan 13, 2010
Messages
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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,
ξ
 

Forum statistics

Threads
1,148,284
Messages
5,745,846
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top