Find and Replace on all worksheets on all files in a folder
I have done this with no success.
Rob Slagle
Jim Thomlinson wrote:
As a guess try being more explicit with your refernces to the
workbook...
Specifically
For Each wks In mybook.Worksheets
HTH
"Rob Slagle" wrote:
I am trying to find and replace 3 things on all worksheets on all
excel
files in a folder.
I have a macro that executes a few API Calls for me to select the
folder for the problematic macro to run in.
here is the code:
Sub TestFile6()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim wks As Worksheet
SaveDriveDir = CurDir
MyPath = GetDirectory(MyPath)
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xnv")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
For Each wks In Worksheets
With wks
Range("A1").Select
Cells.Replace What:="ALF_STATE",
Replacement:="CHARTFIELD1",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ALF_POOL_INDICATOR",
Replacement:="CHARTFIELD2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ALF_REINSURANCE_CD",
Replacement:="CHARTFIELD3", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End With
Next wks
ActiveWorkbook.Save
On Error Resume Next
On Error GoTo 0
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
This is not finding and replacing and I do not know why, I do know
it
is opening and closing each file, I can see that in the taskbar as
the
macro runs.
Any ideas?
Rob Slagle
robslagleATyahooDOTcom
|