none
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.
    Thursday, February 09, 2012 5:24 PM

Answers

  • 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




    Thursday, February 09, 2012 7:19 PM

All replies

  • 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




    Thursday, February 09, 2012 7:19 PM
  • Brill, worked for me :)
    Tuesday, October 02, 2012 1:01 PM