perform operations on a text (.txt) file using excel macro

Question

Hi all,
i am working on a VBA code.
I want to open a text (.txt) file in notepad (Not in excel) using excel VBA. after opening this text file, i want to delete some specific repeated words in that text file. then save this file (after deletion) with a new name with .txt extension.
so my questions are, 1) first of all, is it possible to do using excel VBA??
2) if yes, please get me started with this, share any data, web link or code you have. if No, please suggest what can be other possible way to do this instead of excel VBA.

please suggest, really need your help here.

Answer 1

If you are using Excel VBA, you are using Excel VBA, not notepad. Here is how to do it - update the file names and paths, and (of course) the words to remove.

Sub Sample()
    Dim MyData As String

    '--> Read in the entire text file
    Open "C:\Documents and Settings\File to open.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1

    'Remove all the unwanted words and phrases - add in extra lines as needed

'This could be re-written as a loop to replace an array of words or a range of words entered on a worksheet

MyData = Replace(MyData, "this phrase", "")
    MyData = Replace(MyData, "and this one too", "")
    MyData = Replace(MyData, "word", "")

    '--> Write out the entire text file
    Open "C:\Documents and Settings\New file name.Txt" For Output Access Write As #1
    Print #1, MyData

'Print #1, Application.Trim(MyData)  'use this to remove extra spaces

    Close #1
End Sub

---

HTH, Bernie

Answer 2

Brill, worked for me :)