none
how to read Read text file in VBA RRS feed

  • Question

  • I've excel sheet with some values(key) and one text file which is huge 80 MB. I want read key from excel file and search in text file. Further I wan to extract information from that text file for that key. How can I do that?
    Wednesday, November 16, 2016 11:21 AM

All replies

  • Given that you haven't said what is in the text file or how it relates to the worksheet and what the keys are, it's all a bit up in the air, but maybe the following will get you started

    Sub GetValueFromTextFile()
    Dim iNum As Integer
    Dim strLine As String
    Dim oRng As Range: Set oRng = Range("A1")
    
        iNum = FreeFile()
        Open "c:\Path\Filename.txt" For Input As #iNum
    
        Do While Not EOF(iNum)
            Line Input #iNum, strLine
            If InStr(1, strLine, oRng.Text) > 0 Then
                'Do something with the found line e.g. write it to B1'
                oRng.Offset(0, 1) = strLine
                Exit Do
            End If
        Loop
        Close #iNum
    lbl_Exit:
        Set oRng = Nothing
        Exit Sub
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    Wednesday, November 16, 2016 1:05 PM
  • It depends on how complex the information is that you want to extract.  I would use regular expressions to extract text if it has anything slightly complex.  Google/Bing 'regex' and 'regex vba'.  You will have to invest some time learning regular expressions.
    Wednesday, November 16, 2016 1:17 PM