Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find all folder and/or files in a specific location | Excel Discussion (Misc queries) | |||
Macro Involving Find & Replace In Two Different Files | Excel Discussion (Misc queries) | |||
VBA to find Cell Range in Files in Folder, return value | Excel Programming | |||
Copy several range from all files in folder into several worksheets | Excel Programming | |||
how to step thru only one subfolder of a folder to find like files | Excel Programming |