UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Super Find and Replace    
 
   
randyzapata
post Feb 28 2005, 02:51 PM
Post #1

UtterAccess Addict
Posts: 149



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.
Attached File(s)
Attached File  MoveTextFiles.zip ( 10.45K ) Number of downloads: 10
 
Go to the top of the page
 
+
Luceze
post Feb 28 2005, 03:11 PM
Post #2

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA



Hello again Randy,

See attached. You were having range issues.
Attached File(s)
Attached File  647781-MoveTextFiles.zip ( 9.56K ) Number of downloads: 11
 
Go to the top of the page
 
+
randyzapata
post Feb 28 2005, 04:47 PM
Post #3

UtterAccess Addict
Posts: 149



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

Attached File(s)
Attached File  MoveTextFiles.zip ( 14.4K ) Number of downloads: 6
 
Go to the top of the page
 
+
Luceze
post Feb 28 2005, 04:57 PM
Post #4

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA



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.
Go to the top of the page
 
+
randyzapata
post Feb 28 2005, 05:18 PM
Post #5

UtterAccess Addict
Posts: 149



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?
Go to the top of the page
 
+
Luceze
post Feb 28 2005, 05:28 PM
Post #6

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA



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.
Go to the top of the page
 
+
randyzapata
post Feb 28 2005, 05:43 PM
Post #7

UtterAccess Addict
Posts: 149



Can you please try
A1 = tets
A2 = tets

A2 will not change :(
Go to the top of the page
 
+
randyzapata
post Feb 28 2005, 06:57 PM
Post #8

UtterAccess Addict
Posts: 149



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
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Go to Top  ·  Lo-Fi Version Time is now: 24th May 2013 - 11:57 PM