Replace in Hyperlinks not working

[jomili]

I have a sheet containing about 100 hyperlinks, and for all of them the filepath has changed (IT change). The only part that changed is string "10011". Now it's "10041".

I'm trying a "Replace All" for all of my hyperlinks as shown at https://www.extendoffice.com/documen...hyperlink.html and https://trumpexcel.com/find-hyperlinks-in-excel/.

These were my second choices. My first was to use my "Replace Hyperlinks" macro, which hit an "Out of Memory" error.
The manual methods at the two links resulted in "Not Found", even though I copied the string out of a hyperlink to make sure there was no weirdism in it.

Any idea why Find and Replace wouldn't work?

[PeteABC123]

In the options box, try changing "look in" to "values", or change it to "formulas" and see if it has any effect. Try both, but you've likely tried that, but maybe you haven't.

Pete

Hook'em Horns!

[jomili]

Thanks for trying, but no joy there either. Still not found.

[PeteABC123]

Any chance for a sanitized example in a spreadsheet?

Thanks.

[jomili]

Here you go.

[PeteABC123]

hhmmm. . .

Pete

[Haluk]

Can you try this;

Please Login or Register  to view this content.

[jomili]

Out of Memory

[Haluk]

It works with your sample file on my end....

[jomili]

yep, also works with my sample file, but not my original file.

[Haluk]

Sorry, without seeing the original file i cannot say anything....

[jomili]

Too big to post. Guess I go the manual route...

[Haluk]

Try changing;

Please Login or Register  to view this content.

to;

Please Login or Register  to view this content.

[jomili]

No real difference. Still failing at the "replace" line, "Out of Memory".

However, I have noticed on my original sheet, SOME links were changed (maybe 1/3rd), the rest weren't.

[Haluk]

How many links do you have, in total ?

[jomili]

Added "On error" code, and now it appears to have worked. I went back to my original workbook, last saved version (before I started changing any of them), ran the macro and all were changed. Still don't know what the hiccup was, but I'm good at this moment.

Please Login or Register  to view this content.

[Haluk]

But probably you will need to know which cells produced the error.

So, the following code will insert the text "Error" near to the cells where an error occured...

Please Login or Register  to view this content.

[jomili]

Well THAT was fun! It printed "Error" next to every single cell containing a hyperlink. But it DID change the hyperlinks!

[Haluk]

It was my bad, can you try this;

Please Login or Register  to view this content.

[jomili]

Better: 26 errors out of 70 links. Some of those were links to websites, so I'd expect it couldn't find the "10011", so would error. But of those containing "10011", ALL were changed, but about half had Error next to them.

Remember, this started with Find and Replace, where it could locate NO instances of "10011".

Examples:
Changed, Error next to:
\\share10041\Accounting\Z4116\FY22 4116 Tracker.xlsx
\\share10041\Federal_Funds\_FEDERAL FUNDS DIVISION\IV-E Billings\Copies\Completed 4116s
\\share10041\Budget\SOBUDGET\Active Position Reports\2022\FY 22 Active Positions - Current.xlsx

Changed, no error
\\SHARE10041\Budget\SOBUDGET\1819LAR\Exceptional Item Template & Info\Source Docs\Archive\18_19_85th_Budget_Cost_Estimate_Template (Rev 8-17-16).xlsx
\\SHARE10041\Budget\SOBUDGET\FORMS\Leave Request Form.doc
\\SHARE10041\Budget\SOBUDGET\BCMs

[dani.aniston96]

In Excel, find all hyperlinks that are linked to a specific text.
Go to Home –> Editing –> Find & Select –> Delete –> Delete –> Delete –> Delete –> Delete –> Replace it with...
Click the Options button on the Replace tab of the Find and Replace dialogue box.
Enter 'Company A' in the Find what field.