How to find and replace link paths in multiple Excel files at once

Question

We have a huge folder containing office documents on our server. IT wants to change the name of the folder from Mehper_repository to Planning_reports. Since most of the Excel files in the subfolders are linked to each other, I need to find and replace every single cell content containing \\FileSrv\Mehper_repository\ to \\FileSrv\Planning_reports\. The problem is, Excel's Find/Replace utility can search only within Sheet or Workbook. How can I search within a folder/subfolders just looking in Formulas and replace that expression?

Answer 1

I would suggest the commercial product PowerGREP :

PowerGREP is a powerful Windows grep tool. Quickly search through large numbers of files on your PC or network, including text and binary files, compressed archives, MS Word documents, Excel spreadsheets, PDF files, OpenOffice files, etc. Find the information you want with powerful text patterns (regular expressions) specifying the form of what you want, instead of literal text. Search and replace with one or many regular expressions to comprehensively maintain web sites, source code, reports, etc. Extract statistics and knowledge from logs files and large data sets.

Answer 2

Building off TomWij's answer, loop through all the cells, looking for references to "\FileSrv\Mehper_repository\" and replace with "\FileSrv\Planning_reports\". You might be able to use the Cells collection to do it faster, but that might muck around with the formulae in bad ways (especially since that string won't be isolated).

Try asking about writing VBA like this on StackOverflow, you're more likely to get a programming-related response there than on SuperUser.

Answer 3

A macro approach:

I can't answer this exactly, but what about using a macro program so the only thing you have to do is opening the files one-by-one and running the macro? That might be feasible if the collection is not too large, if not, you will probably have to come up with programming a custom solution...

---

Programming a custom automatic solution:

Building off A. Scagnelli's answer, you can take your favorite .NET language and do (C# examples):

• Recursively enumerate subdirectories.
• Enumerate each file in all directories found, filter on the excel extension.
• Automate Microsoft Excel in this way: http://support.microsoft.com/kb/302084
• And replace the text in this way: How Can I Replace Text in an Excel Spreadsheet?

Pseudo code:

openExcel

// Fills the list recursively with aboslute paths of the subfolders of path.
enumerateFolder(list, path)

// Enumerate paths. 
foreach(path in list)
{
   // Enumerate files
   foreach(file in path)
   {
      openFileInExcel
      replaceFileContents
      closeFileInExcel
   }
}

closeExcel

Explanation:

Just pass the path to the parent directory and a reference to a list along when you call them recursively. This allows you to get a list of folders which files you can enumerate in a double loop.

Leave Excel open during the whole algorithm, just 'open, replace and close' in the inner loop.