Repeating 'replace' function in a group of text files 2

[DonyBoy]

I have a folder full of text files. Each of these file has a phrase that needs replacing by another phrase (same phrase for each file).It will take me ages to open each of these text files and do a replace function. Is there a way to create an excel file with macro set up such that

1) in Column A- I enter the names of each of these files, eg. file1.txt

2) In cell B1, I write which phrease needs replacing

3) in Cell B2, I write what I replace it with.

Can I set it up such that I keep this excel file in the same folder where the text files are, so that I don’t have to mess with folder names each time I want to use this file.

any help will be greatly appreciated.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

When in doubt, use your macro recorder.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[DonyBoy]

I don't even know how to 'open' a text file within Excel. I have used some VBA, but it's all within 1 excel sheet. The idea of opening an external file is beyond my knowledge.

 

[Bong]

To open a text file (remember, VBA is plain old BASIC, except for V and A):

Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]

The Open statement syntax has these parts:

Part Description
pathname Required. String expression that specifies a file name — may include directory or folder, and drive.
mode Required. Keyword specifying the file mode: Append, Binary, Input, Output, or Random. If unspecified, the file is opened for Random access.
access Optional. Keyword specifying the operations permitted on the open file: Read, Write, or Read Write.
lock Optional. Keyword specifying the operations restricted on the open file by other processes: Shared, Lock Read, Lock Write, and Lock Read Write.
filenumber Required. A valid file number in the range 1 to 511, inclusive. Use the FreeFile function to obtain the next available file number.
reclength Optional. Number less than or equal to 32,767 (bytes). For files opened for random access, this value is the record length. For sequential files, this value is the number of characters buffered.

Then, to read:

Input #filenumber, varlist

The Input # statement syntax has these parts:

Part Description
filenumber Required. Any valid file number.
varlist Required. Comma-delimited list of variables that are assigned values read from the file — can't be an array or object variable. However, variables that describe an element of an array or user-defined type may be used.

And to write:

Write #filenumber, [outputlist]

The Write # statement syntax has these parts:

Part Description
filenumber Required. Any valid file number.
outputlist Optional. One or more comma-delimited numeric expressions or string expressions to write to a file.

_________________
Bob Rashkin

 

[Swi]

You could also use the FileSystemObject to loop through your folder and then read and write your files. I suggest you read up on it to understand the code below.

Private Sub Command1_Click()
Dim fso As FileSystemObject
Dim fso_folder As Folder
Dim fso_file As File
Dim InStream As TextStream
Dim OutStream As TextStream
Dim InputData As String

Set fso = New FileSystemObject

Set fso_folder = fso.GetFolder("C:\Test")

For Each fso_file In fso_folder.Files
    Set InStream = fso.OpenTextFile(fso_file, ForReading)
    InputData = InStream.ReadAll
    InStream.Close
    Set OutStream = fso.OpenTextFile(fso_file, ForWriting, True)
    OutStream.Write Replace(InputData, "TEXT TO REPLACE", "REPLACEMENT TEXT")
    OutStream.Close
Next fso_file

Set InStream = Nothing
Set OutStream = Nothing
Set fso_folder = Nothing
Set fso_file = Nothing
Set fso = Nothing
End Sub

Here is a link to get you started.

http://msdn2.microsoft.com/en-us/library/6kxy1a51.aspx

Swi