My Assistant
![]() ![]() |
|
|
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)
|
|
|
|
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)
|
|
|
|
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)
|
|
|
|
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. |
|
|
|
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?
|
|
|
|
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. |
|
|
|
Feb 28 2005, 05:43 PM
Post
#7
|
|
|
UtterAccess Addict Posts: 149 |
Can you please try
A1 = tets A2 = tets A2 will not change :( |
|
|
|
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 Top · Lo-Fi Version | Time is now: 24th May 2013 - 11:57 PM |