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

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

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?

Asked by: Guest | Views: 309

Total answers/comments: 2

Comments display order:

bert [Entry]

"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."

bert [Entry]

"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."