Full Version: Super Find and Replace
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
randyzapata
I am trying to open up text files one at a time then go through the main speadsheet doing a find and Replace on the text file. I am have problems getting it to work. I do not think my Ranges are name correctly. Can someone please help me. Note you will have to make a text file in the same location of this shredsheet.
Luceze
Hello again Randy,

See attached. You were having range issues.
randyzapata
The program that you helped with above does not search each word in Excel, it does it by each line. I made an addition to my program by opening the text files in word (not sure if this is a good idea or not, but it is the only way I can think of to get my find and replace done properly). I got my program to work by enableing word under tools/referances, but for some reason it is now locking up. I still have not been able to fully test it because of the locking up. In order to stop it I have to go into the task maniger and end WINWORD.EXE.

CODE
Sub MoveTextFiles()
'
' formatS Macro
' Macro created 02/28/2005 by Randall

Dim wrdApp As Word.Application, wbXL As Excel.Workbook
Dim wrdDoc As Word.Document
Dim tString As String, tRange As Word.Range
Dim p As Long, r As Long
Set wrdApp = CreateObject("Word.Application")

Application.WindowState = xlMinimized
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Application.WindowState = wdWindowStateMinimize
  Set sht1 = ThisWorkbook.Sheets("Sheet1")

Set fso = CreateObject("Scripting.FileSystemObject")

    
    'Set the array for the number of strum counter
    'The limit is set to 200  STRUMs
    'The number may be raised if you happend to have more than 200 STRUM
    Dim TextFiles(1000) As Variant
    Dim cl As Range
    'finds current document name
    MainDocPath = Application.ActiveWorkbook.Path
    
        
    'Searches for all text files in folder
    With Application.FileSearch
        .NewSearch
        .LookIn = MainDocPath
        .SearchSubFolders = True
        .Filename = "*.txt"
        .MatchAllWordForms = False
        .Execute
            
        For i = 1 To .FoundFiles.Count
            'Workbooks.Open Filename:=.FoundFiles(i)
            'FFName = ActiveWorkbook.Name
    '*************locks up here*****************

            'wrdApp.Visible = True
            Set wrdDoc = wrdApp.Documents.Open(.FoundFiles(i))
            With sht1
                For Each cl In .Range("A2", .Range("A65536").End(xlUp))
                    
            
                    With wrdDoc
                        Selection.Find.ClearFormatting
                        Selection.Find.Replacement.ClearFormatting
                        With Selection.Find
                            .Text = cl.Value
                            .Replacement.Text = cl.Offset(0, 1)
                            .Forward = True
                            .Wrap = wdFindContinue
                            .Format = False
                            .MatchCase = False
                            .MatchWholeWord = False
                            .MatchWildcards = False
                            .MatchSoundsLike = False
                            .MatchAllWordForms = False
                        End With
                        Selection.Find.Execute Replace:=wdReplaceAll
                    End With
                Next cl
               With wrdDoc
                    ChangeFileOpenDirectory MainDocPath
                    ActiveDocument.SaveAs Filename:=ActiveWorkbook.Name, FileFormat:=wdFormatText, _
                        LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
                        :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
                        SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
                        False, Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False _
                        , LineEnding:=wdLFOnly
                    .Close ' close the document
                 End With
                
            End With

        Next i
    End With
    
Application.ScreenUpdating = True
Application.WindowState = xlMaximized
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Close
Application.DisplayAlerts = wdAlertsAll

End Sub
Luceze
QUOTE
The program that you helped with above does not search each word in Excel, it does it by each line.


Not sure what you mean by this. The find and replace function will replace each instance of the search string with your replacement string, that is, unless you set the LookAt argument to xlWhole.

Not much of a word programmer. Can take a look at it later if that is the way you want to go.
randyzapata
Ok I went over the code that you helped me with and it only does a find in cell A1. Do you know how to fix this?
Luceze
Make sure this piece of code looks like the following:
CODE
                    With ActiveSheet.Range("A1", ActiveSheet.Range("B65536").End(xlUp))
                        .Replace What:=cl.Value, Replacement:=cl.Offset(0, 1).Value, _
                        SearchOrder:=xlByColumns, MatchCase:=True
                    End With


The example that I attached above worked fine on my machine.
randyzapata
Can you please try
A1 = tets
A2 = tets

A2 will not change :(
randyzapata
I am not sure if this is the fastest thing to use, but I got the followinf to work:

ActiveSheet.Columns("A").Replace.......


If any one know a better way then please let me know
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.