Deleting strings from formulas

I have an Excel workbook that references a custom add-in located on a users C drive. However, if a user opens this workbook from a shared network drive (W drive for example) instead, the workbook then looks for the add-in on the W drive and can't locate it, resulting in a bad path name in the formula bar and a #NAME? error in the cell.

Ex:

='W:\Path\Path\AddIn.xla'!ABCFunction($D$9,$B$5,$B$6,$B$7,"hours", 0)

...where 'W:\Path\Path\AddIn.xla'! needs to be removed for the function to work properly.

This can be fixed by doing a simple find/replace of the offending link in the formula bar, but I'm looking for a way to automate this process. Because there are multiple shared network drives this workbook might be opened from, I can't use a simple ChangeLink function because the path will change depending on which drive it is opened from.

I need a way to either find any characters in between the = and ABCFunction and delete them, or a way to find any link's ending in .xla and delete.

Any help would be greatly appreciated.

Thanks!

Easily done.

Please provide Workbook and worksheet names

Well it's not just one workbook in particular, there are multiple that have this same issue. I was just thinking of using an ActiveWorkbook call.

Ok so you will save the macro in your personal folder so it is always available.

How will you ensure that it isn't run on the wrong worksheets? or do the strings only occur on the sheets that you want modified?

I was just going to copy/paste the code under the Private Sub Workbook_Open() method for each workbook I need modified.

ok

I will be 5 minutes.

BRB

Ok

Try This code.

Please Login or Register  to view this content.

Thank you for the reply!

I tried this code and got an error saying "You cannot change part of an array". I probably should have mentioned these formulas are all within an array.

Which object/procedure should this go under? I want this to run when the workbook is opened without any additional users interaction. That's why I initially had it under Private Sub WorkBook_Open().