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 |
Find and Replace on all worksheets on all files in a folder
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 |
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 |
Find and Replace on all worksheets on all files in a folder
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" wrote in message ups.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 |
All times are GMT +1. The time now is 12:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com