Replacing file path in excel with another

Question

I have an excel file with cells that retrieve data from a file stored on another computer. I need to update the file path in all of these formulas but it is extremely tedious as each time I update the formula, an open file window comes up. The location that I am changing this to is also not on my computer.

Is there a quick way to update a file path formula without having this dialog window open up?

I need to change my path from

='\\clusfs001nas\

To:

='R:\

Answer 1

Under the "Data" Tab, click "Edit Links" - this should show you the files you have linked to, and you can "Change Source" to update it. Alternatively, you could do a simple Find/Replace (CTRL+F, then click "replace" and type the path you need to replace and then in the replace area, put the new path).

Answer 2

You can change formulas using VBA.

But first, I suggest you have it in only one cell, and in the formulas, you add this cell, not a real path. This way, next time you need to change it, you change only one cell.

Now the coding, an example to change A2 and B5:

Sub Change()
    Range("A2").Formula = type the formula here between quotes
    Range("B5").Formula = type the formula here between quotes
end sub

If you have lots of cells in a column, you could do a loop:

For i = 1 to 20 'say you have cells from row 1 to 20
    Cells(i,3).Formula = type the formula here between quotes
    'the number 3 above is the third column: C
next

Answer 3

This is late, but one option that can avoid the annoying dialog when you click Replace All is to insert some random characters at the beginning of the path which will prevent Excel from thinking that the string is an actual link. Then you should be able to edit the portions of that path that need to be changed, and once that is complete, do another Replace ALL, basically removing the random characters that were inserted.

You need to be careful of course, because if you choose a random character that happens to be within other formulas the Replace ALL may have some unexpected results

Answer 4

You can write on your code:

Application.DisplayAlerts = False
Application.AskToUpdate = False