Excel VBA

Excel VBA
Excel Formulas
User-defined Functions
Office Links
Access VBA
Access SQL
Alanís Excel FAQ
Excel Home
Alan’s Home

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
        Loop
        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:

Title: SOMETHING

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-2014

Updated:  03/27/2014 19:05
This page added:  17 August 2002