Excel VBA

css navigation by Css3Menu.com

Extract Info from Text File

I have 90,000 rows of information in an excel file and 90,000 text files. The path of the text file is in the first column of every row, and I want to extract specific data from the text file and enter this information into the specific cells.
Option Explicit

Public xR, zZ As Long, daRows

Sub getFields()
    daRows = Application.CountA(ActiveSheet.Range("A:A"))   'Determine # of rows
    For zZ = 2 To daRows        'Set up loop
        Open Cells(zZ, 6) For Input Access Read As #6   'Open file to be read
        Do While Not EOF(6)
            Line Input #6, xR   'Read a line
                If Left(xR, 7) = "Title: " Then
                    Cells(zZ, 2) = Right(xR, Len(xR) - 7)
                End If
                If Left(xR, 8) = "Author: " Then
                    Cells(zZ, 4) = Right(xR, Len(xR) - 8)
                End If
                If Left(xR, 9) = "Subject: " Then
                    Cells(zZ, 3) = Right(xR, Len(xR) - 9)
                End If
        Close #6    'Close the file
    Next    'Move to next line
End Sub

The example I was given had specific information on 3 lines in the text document:


Author: UZAKOV

Subject: 1961. ZOOL. ZH., MOSCOW

So, we simply get the text path and file name from A, open it and start looking for certain phrases at the beginning of the line. When found, write to the appropriate cell next to the path statement. The

Right(xR, Len(xR) - 8)
statement tells it to take the right side of the phrase complete except the first 8 characters, etc.

© 2002-2024

Updated:  04/17/2024 12:26
This page added:  17 August 2002