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

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

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
>
>

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
> >
> >

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.

--
Regards,
Tom Ogilvy

"Rob Slagle" <Rob.Slagle@gmail.com> wrote in message
news:1114607019.599208.53370@l41g2000cwc.googlegroups.com...
> 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
> > >
> > >
>