Removing links to other documents on a specific sheet in workbook

[mcomp72]

I have created a workbook that is sometimes used by multiple people for the same project. Thus, I have built in a way for the user to import data from one workbook to another, as they may be using different files between them, and ultimately someone will need to "marry" the data together into one file.

On one particular sheet in the workbook, sometimes a user might have formulas that links to another workbook. I don't want these links to be imported, so I need code to delete them, BUT I don't want to delete the rest of the formula.

For instance, if the link in the formula is:

='C:\Users\matt\Desktop\[Test Workbook.xlsx]My Sheet'!$B$2

Then I want to change the formula to:

='My Sheet'!$B$2

I thought about using ".BreakLink", but I believe this will break links across the entire workbook, and I don't want to do that. I only want to do it on one specific sheet.

Oh, and this would need to work on both Windows & Mac. But if you have thoughts for how to make it work on Windows, I may be able to modify the code, if necessary, to make it work on Mac.

 

[rondeondo]

it is possible to search and replace within formulas.
so in that sheet do replace
find: 'C:\Users\matt\Desktop\[Test Workbook.xlsx]My Sheet'!
replace with 'My Sheet'!
click on options and set "Look In:" to formulas
then do replace all.