none
Notepad to Notepad Macro coding RRS feed

  • Question

  • Hi Team,

    I having a various notepad file in a particular folder. i need to read the notepad file and search for a particular string (inside the string i need to search another string and it have to end with another search string). If it founds the string in the notepad file. After that i need to copy the entire content from the start string to end of the string and it has to paste in new notepad sheets. kindly help me

    eg:

    report id -------------> first search string

    job-id ----------> second search string

    end of report--------> final string to be end 

    after search it had to paste in new notepad sheets

    Friday, November 7, 2014 6:54 AM

Answers

  • Ok, this isn't too difficult, but it will take a bit of effort.  I did the same thing earlier this week, for a project at work, and I'll share my solution with you.

    First, make sure you understand how to open a text file, and read the contents.  Here are a few links that demonstrate how to do that.

    http://mrspreadsheets.com/1/post/2013/09/vba-code-snippet-22-read-entire-text-file-into-string-variable.html

    http://stackoverflow.com/questions/20390397/reading-entire-text-file-using-vba
    http://www.ozgrid.com/forum/showthread.php?t=28262

    http://www.excelforum.com/excel-programming-vba-macros/332741-getting-text-file-into-a-vba-string-variable.html

    http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27980851.html

    Ok, after you open the text file and read the contents, parse the string (the entire contents of the text file) according to your business requirements.  Try something like this . . .

    Function:

    Public Function ImportTextFile(StrFile As String) As String
        Open StrFile For Input As #1
        ImportTextFile = Input$(LOF(1), 1)
        Close #1
    End Function

    Dim StrFile As String

    RowNumber = Range("A65536").End(xlUp).row + 1

    strVar = ImportTextFile(Selection)  ' This will import the text file into a variable named 'strVar'

    Then, start looking for your specific text . . .

                If strVar Like "*EXPIRE*" Then
                    LPosition = InStr(1, strVar, "EXPIRE")
                    LPosition = LPosition + 21
                    Position = Mid(strVar, LPosition, 10)
                    Range("C" & RowNumber).Value = Position
                    LPosition = 0
                End If
                LPosition = 0
                RPosition = 0

    RowNumber = RowNumber + 1

    InStr() looks for text in a string, start at position1, look at everything in your 'strVar' variable, and look for specific text . . . I'm looking for 'EXPIRE'

    I find the LPosition and then move 21 characters to the right.

    I find the exact string that I need using the Mid() function.  I assign the result to the variable named 'Position'.

    Position = Mid(strVar, LPosition, 10)

    Finally, take the variable named 'Position' and write it to Cell C1.

    Range("C" & RowNumber).Value = Position

    In my case, I'm looping through hundreds of text files, opening each, reading the contents of each, closing each.  Thus, I'm looping through lots of records (the paths to the text files) in my ColumnA.

    RowNumber = Range("A65536").End(xlUp).row + 1

    RowNumber = 1
    For Each cell In rng

    OPEN TEXT FILES

    RowNumber = RowNumber + 1

    Next cell

    Try that and see how you get along.  Post back with specific questions.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, November 7, 2014 3:23 PM

All replies

  • Ok, this isn't too difficult, but it will take a bit of effort.  I did the same thing earlier this week, for a project at work, and I'll share my solution with you.

    First, make sure you understand how to open a text file, and read the contents.  Here are a few links that demonstrate how to do that.

    http://mrspreadsheets.com/1/post/2013/09/vba-code-snippet-22-read-entire-text-file-into-string-variable.html

    http://stackoverflow.com/questions/20390397/reading-entire-text-file-using-vba
    http://www.ozgrid.com/forum/showthread.php?t=28262

    http://www.excelforum.com/excel-programming-vba-macros/332741-getting-text-file-into-a-vba-string-variable.html

    http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27980851.html

    Ok, after you open the text file and read the contents, parse the string (the entire contents of the text file) according to your business requirements.  Try something like this . . .

    Function:

    Public Function ImportTextFile(StrFile As String) As String
        Open StrFile For Input As #1
        ImportTextFile = Input$(LOF(1), 1)
        Close #1
    End Function

    Dim StrFile As String

    RowNumber = Range("A65536").End(xlUp).row + 1

    strVar = ImportTextFile(Selection)  ' This will import the text file into a variable named 'strVar'

    Then, start looking for your specific text . . .

                If strVar Like "*EXPIRE*" Then
                    LPosition = InStr(1, strVar, "EXPIRE")
                    LPosition = LPosition + 21
                    Position = Mid(strVar, LPosition, 10)
                    Range("C" & RowNumber).Value = Position
                    LPosition = 0
                End If
                LPosition = 0
                RPosition = 0

    RowNumber = RowNumber + 1

    InStr() looks for text in a string, start at position1, look at everything in your 'strVar' variable, and look for specific text . . . I'm looking for 'EXPIRE'

    I find the LPosition and then move 21 characters to the right.

    I find the exact string that I need using the Mid() function.  I assign the result to the variable named 'Position'.

    Position = Mid(strVar, LPosition, 10)

    Finally, take the variable named 'Position' and write it to Cell C1.

    Range("C" & RowNumber).Value = Position

    In my case, I'm looping through hundreds of text files, opening each, reading the contents of each, closing each.  Thus, I'm looping through lots of records (the paths to the text files) in my ColumnA.

    RowNumber = Range("A65536").End(xlUp).row + 1

    RowNumber = 1
    For Each cell In rng

    OPEN TEXT FILES

    RowNumber = RowNumber + 1

    Next cell

    Try that and see how you get along.  Post back with specific questions.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, November 7, 2014 3:23 PM
  • hi ryguy72,

    thanks for your response, but the above code doesn't work for me it always shows error while compiling. can i have the entire code for the above problem

    Monday, December 8, 2014 7:26 AM
  • That's basically it.  There is a little more, but it's customized for a couple specific things I do; that won't help you at all.

    Why don't you post your code, and we'll fix that.  My code works fine for me; there's nothing to fix on my end.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, December 8, 2014 4:44 PM
  • Option Explicit

    Sub ReadTxtFile()

        Dim start As Date
        start = Now

        Dim oFSO As Object
        Set oFSO = CreateObject("Scripting.FileSystemObject")

        Dim oFS As Object

        Dim filePath As String
        filePath = "C:\Users\Desktop\abc.txt "

        Dim arr(100000) As String
        Dim i As Long
        i = 0

        If oFSO.FileExists(filePath) Then

            On Error GoTo Err

            Set oFS = oFSO.OpenTextFile(filePath)
            Do While Not oFS.AtEndOfStream
                arr(i) = oFS.ReadLine
                i = i + 1
            Loop
            oFS.Close
        Else
            MsgBox "The file path is invalid.", vbCritical, vbNullString
            Exit Sub
        End If

        For i = LBound(arr) To UBound(arr)
            If InStr(1, arr(i), "vignesh", vbTextCompare) Then
                Debug.Print i + 1, arr(i)
                Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1) = i + 1
                Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = arr(i)
           End If



        Next



        Debug.Print DateDiff("s", start, Now)

        Exit Sub

    Err:
        MsgBox "Error while reading the file.", vbCritical, vbNullString
        oFS.Close
        Exit Sub

    End Sub

    hi ryguy,

     i am having the above code to read the text file ans search the text as "vignesh" from that notepad. i need to complete my search in "end sub". but i cant able to code it.

                                        
    Tuesday, December 9, 2014 4:04 AM