Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted
+50

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.

image

share|improve this answer
add comment

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.

share|improve this answer
add comment

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):

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.

share|improve this answer
    
Thanks for your suggestion, Tom. The problem is, the collection is too large. So I guess I'll need a programming solution. –  Mehper C. Palavuzlar Aug 11 '10 at 6:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.