Find and Replace on all worksheets on all files in a folder

[Rob Slagle]

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

 

[Guest]

As a guess try being more explicit with your refernces to the workbook...
Specifically

For Each wks In mybook.Worksheets

HTH

 

[Rob Slagle]

I have done this with no success.

Rob Slagle

As a guess try being more explicit with your refernces to the workbook...
Specifically

For Each wks In mybook.Worksheets

HTH

 

[Tom Ogilvy]

See it this works:

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 MyBook.Worksheets
With wks
' Range("A1").Select

wks.Cells.Replace What:="ALF_STATE", _
Replacement:="CHARTFIELD1", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

wks.Cells.Replace What:="ALF_POOL_INDICATOR", _
Replacement:="CHARTFIELD2", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

wks.Cells.Replace What:="ALF_REINSURANCE_CD", _
Replacement:="CHARTFIELD3", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
Next wks
Mybook.Save
On Error Resume Next
On Error GoTo 0
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

I believe will help.