replace text string automatically in cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How could I replace a specific text string in thousands of files without
opening them ???
Thanks.
 
Basically, using Excel you're not going to accomplish that without opening
them in some fashion. Obviously, since you're talking about thousands of
files, you're going to want to do it with automation (VBA). The code to do
it isn't that complex, but with thousands of files most likely scattered
across many folders, if not many physical disks, it is going to be somewhat
time consuming.

Would you care to tells us more about the situation: what type of files are
you talking about? .xls? .doc? .txt? another type or even a mix of types?
Perhaps some information about where all of these files are located (one
folder, many folders on one physical drive, or other situation).
 
..xls files in one physcial drive, also I would like to remove inserted
picture if any.
Tks
 
Ok, you asked for it. First caution: I'd at least test on a batch of files
that are copies of the originals to see if the results are really as you want
them to be. The find and replace is a "destructive" process, plus changes
made via VB code cannot be undone.

How it works: put a copy of the workbook with this code in it into any
individual folder with other .xls files to be processed then run the Macro in
it. Go take a coffee break - the time to complete will be dermined primarily
by:
the speed of your computer,
the number of files to be processed,
the number of worksheets in each of the workbooks.
It will open each .xls file in the folder that it is placed into in turn,
'flip' through all of the worksheets in each of those .xls files, delete any
pictures or linked pictures, and then do a massive Edit|Replace All action on
each sheet. After finishing with a workbook, it saves it with the changes
and moves on to the next one.

IT WILL FAIL: if it hits a protected worksheet in a workbook. If this is
going to be an issue, let me know and we'll try to come up with a
work-around: we can hope that they are either protected without a password,
or that the password is the same for all protected sheets in all workbooks.

How to get the code into a workbook for use:
Create a new, empty workbook. Press [Alt]+[F11] to enter the VB Editor.
Choose Insert | Module from the VBE menu bar. Cut and paste the code below
into the empty module. Save the workbook into a folder with other .xls files
to be processed. You can now run the macro MassiveCleanupEffort from Tools |
Macro | Macros. When you copy the code into the VB module, change the values
for the two constants:
Const findString
Const replString
to contain the exact text you want to find and be replaced with. If you
wish to have the findString replaced with 'nothing' set it = "".


Sub MassiveCleanupEffort()
'works on all .xls files in same
'folder with this file

'change these constants as required to
'accomplish the text changes needed
'type it EXACTLY as it appears in the
'workbook: case, punctuation and spacing must be
'exactly as it will be found within the worksheets
Const findString = "text to be replaced"
Const replString = "Put This in its Place"

Dim WB As Workbook
Dim WS As Worksheet
Dim searchRange As Range
Dim anyShape As Shape
Dim anyXLFile As String
Dim basicPath As String
Dim wbCount As Long

basicPath = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, "\"))

anyXLFile = Dir$(basicPath & "*.xls")
Application.ScreenUpdating = False ' for improved speed
Do While anyXLFile <> ""
If anyXLFile <> ThisWorkbook.Name Then
Workbooks.Open basicPath & anyXLFile
wbCount = wbCount + 1
Set WB = ActiveWorkbook ' work in memory for speed
For Each WS In WB.Worksheets
'remove any pictures or linked pictures first
If WS.Shapes.Count > 0 Then
For Each anyShape In WS.Shapes
'if a shape is a Picture (13) or
'if it is a linked picture (11) then
'remove it from the sheet
If anyShape.Type = 13 Or _
anyShape.Type = 11 Then
anyShape.Delete
End If
Next ' individual shape testing loop
End If 'test for shapes
Set searchRange = WS.UsedRange
searchRange.Replace What:=findString, _
Replacement:=replString, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=True, _
SearchFormat:=False, _
ReplaceFormat:=False
Next ' worksheet loop
Set WB = Nothing ' release resource
'save the workbook with changes
ActiveWorkbook.Close True
End If
anyXLFile = Dir$() ' get next possible filename
Loop ' Excel files loop
Application.ScreenUpdating = True
MsgBox "All " & wbCount & " .xls files in this folder have been processed."
End Sub
 
Back
Top