Macro to remove external workbook reference from Formula

Im currently working on a spreadsheet that some others have been bringing sheets from external workbooks into.
The problem is I need to remove the external workbook references, but keep the sheet and cell reference in the formula.
Is it possible to run a macro that will convert all formulas with external links to sheets in the local workbook.
The sheet references and cell references are correct I just need to loose the Workbook references.
I dont want to break these links either.

They conveniently put a password on the workbook, hid some sheets then forgot the password.

Visibility is only set to 0-xlsheethidden. But as the workbook is locked I cant access the sheets with external refderences.

I cant update and reassign links as some wonderful person decided to lock the workbook and hide some sheets, and not give me the password.

If you can't do it manually because of protection, then code won't be able to do it either.