Search/Replace across multiple files

Posted by Mark Parsons on January 31, 2001 5:59 AM

Houston - We have a problem!

We've got 40 separate workbooks (i.e. actual files, not sheets!) where one (one! argh!) item of text is consistently wrong on each - exactly the same place, exactly the same error.

Is there any way to either replace the error in a selection of files (don't think that there will be...) or possibly automate the loading of each workbook (all in one directory), change the cell, and re-save?

I had a bash using VBA, but couldn't quite get my brain around it! ;-/

Ta very much!

Posted by Grant on February 02, 2001 2:15 AM

Hi Mark,

I'm no VB expert but you can automate a task like this fairly easily.

If you go to tools, record new macro in a blank or main workbook, then go through opening, changing, saving and closing one of the problem files.

Next go to VB (ALT F11) into modules & double click module1 to view the code.

You now have the code to change one file. To do this for all files, set up a loop:

Sub Macro1()
for i=1 to 40 'Repeats 40 times
All the code in the macro
Next i
End Sub

This will change 1 file 40 x. to make the change on all files, it depends on how the files are named: if they are "File1.xls","File2.xls"... things are easier. if they are different "myfile.xls", "another.xls"... more complex.

1st case, after "for" line, put:
open_filename = "File" & i & ".xls"
2nd case,
open_filename = Choose(i,"Myfile.xls", "another.xls",...)

then change the filename in the open, close and save statements to open_filename, eg:
workbooks.open Filename:=open_filename

Hope this helps!

P.S. test the macro on only 2 or 3 files to start with not all 40.