MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Batch find/replace?


Posted by Jeff on August 10, 2001 4:41 PM

Here is my dilemma:
I have about 1000 files that are essentially the same xls file, with different data. Every one of them has a global variable in them that I need to change now.
I thought maybe I could use a text search and replace since the string was the same size, but that part of the file is not in plain text!

Guess I need to write a macro to do it, but how?

Thanks for any help you can offer,
Jeff


Posted by Ivan F Moala on August 11, 2001 7:23 AM

Jeff
1) By global variable do you mean a Named/defined
range ?
2) Have you written a macro ??

If YES to both then to get you started;

1) If you haven't already, then place all files
in a dir
2) Open all files via a loop 1 by 1
3) do your search
4) close & save
5) Loop

eg.

Sub LoadandChange()
Dim fs
Dim LI As String
Dim i As Integer

LI = "C:\Excelfiles"
Set fs = Application.FileSearch

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error GoTo ErrH
With fs
.LookIn = LI
.Filename = "*.xls"
If .Execute <> 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i)
ActiveWorkbook.Names("test").Delete
ActiveWindow.Close True
Next i
Else
MsgBox "There were no files found " & LI
End If
End With

ErrH:
If ActiveWorkbook.Name <> ThisWorkbook.Name Then ActiveWorkbook.Close False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
If Not Err Then Exit Sub
MsgBox Err.Number & ":=" & Err.Description, vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext

End Sub


NB: just an example not fully tested


Ivan

Posted by Jeff on August 13, 2001 2:51 PM

Ivan,
Big thanks for your help! I wasn't too clear when i said what i wanted to replace, sorry about that. I acually need to replace a line of macro code.

Thanks,
Jeff