Search text file for keywords and input information into excel columns-VBForums
Results 1 to 2 of 2

Thread: Search text file for keywords and input information into excel columns

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2012
    Posts
    1

    Search text file for keywords and input information into excel columns

    I am currently working on a program that can search through multiple text files and be able to search for certain Strings, obtain the information after the string, and put it into an excel document.
    For example:

    Name: Brian
    Height: 6'0
    Weight: 200 lb

    Name: Kevin
    Height: 5'7
    Weight: 170 lb

    However, because the length of each text file differs and there is a lot of information in the text file that isn't required, I'd like to be able look for the Keywords: Name, Height, Weight, and output the information into excel columns.
    Ideas on how to do this would be appreciated.

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Search text file for keywords and input information into excel columns

    Welcome to the forums smilestatue

    If the names are unique, and the Height and the Weight is right below the Name always then you can try this method

    Logic:

    1. Read the file in an array in one go
    2. Loop through the array to find the name and then pick up the values.

    Code:

    Code:
    Option Explicit
    
    Sub Sample()
        Dim MyData As String, strData() As String
        Dim Nm As String, Ht As String, Wt As String
        Dim i As Long
        
        Open "C:\Sample.Txt" For Binary As #1
        MyData = Space$(LOF(1))
        Get #1, , MyData
        Close #1
        strData() = Split(MyData, vbCrLf)
        
        Nm = "Kevin"
        
        For i = LBound(strData) To UBound(strData)
            If Trim(strData(i)) = "Name: " & Trim(Nm) Then
                Ht = strData(i + 1)
                Wt = strData(i + 2)
                Exit For
            End If
        Next i
        
        Debug.Print Nm: Debug.Print Ht: Debug.Print Wt
    End Sub
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.