Encontré este código por @Scott Holtzman y necesito modificarlo un poco para satisfacer mis necesidades. Este código toma cada línea en un archivo de texto y la coloca en columnas separadas en una hoja de Excel (A1, B1, C1, etc.), cada archivo de texto se almacena en una fila separada (1,2,3 y así sucesivamente). Primero, quiero que solo coloque texto en la hoja de Excel si la línea comienza con un texto específico; segundo, quiero que solo copie parte del texto de cada línea en la hoja de Excel.

Sub ReadFilesIntoActiveSheet()

Dim fso As FileSystemObject
Dim folder As folder, file As file, FileText As TextStream
Dim TextLine As String, Items() As String
Dim i As Long, cl As Range

' Get a FileSystem object
Set fso = New FileSystemObject

' get the directory you want
Set folder = fso.GetFolder("D:\YourDirectory\")

Dim x As Long
x = 1 'to offset rows for each file

' Loop thru all files in the folder
For Each file In folder.Files

' set the starting point to write the data to
Set cl = ActiveSheet.Cells(x, 1)

' Open the file
Set FileText = file.OpenAsTextStream(ForReading)

Dim j As Long
j = 0 'to offset columsn for each line
' Read the file one line at a time
Do While Not FileText.AtEndOfStream

    TextLine = FileText.ReadLine 'read line

    cl.Offset(, j).Value = TextLine 'fill cell

    j = j + 1
Loop

' Clean up
FileText.Close

x = x + 1

Next file

Set FileText = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing

End Sub

Así es como se ven mis archivos de texto:

From:NameName           'want all text except the "FROM:"
Date:yyyy.mm.dd         'want all text except the "Date:"
Type: XXXXXXXXX         ' I don't want this line into excel
To: namename            ' I don't want this line into excel

----------------------------- xxxxxxx ---------------------
A1: Tnr xxxxxxxxxxxxx   'want all text except the "A1: Tnr" only next 13char
A2: texttext            'want all text except the "A2:"
An:                     'A1 and up to A14
A14: texttext           'want all text except the "A14:"  

------------------------------ xxxxxx ----------------------

Entonces, en total hay 22 líneas en el archivo de texto.

Y si es posible usar FROM :, DATE :, A1: to A14: como encabezados en la primera fila, eso sería épico.

He intentado buscar mi camino en Google, e intenté un poco con esto:

TextLine =    FileText.ReadLine 'read line
If InStr(TextLine, "A1:") 

pero eso funciona solo para una línea y parece que no puedo hacer que funcione con varias líneas. Además, coloca la salida en la celda F1, en lugar de A1. creo que esto se debe a que cada línea en el documento de texto obtiene una celda, incluso si no hay nada escrito en ella.