Extract two fields from text files

[YasserKhalil]

Hello everyone
I need a macro that enables me to select a folder not files and in this folder there will be text files ..
I have attached sample of these text files and I need to extract two fields only : The first after the string "HTT"
For example :

[HTT "First Field - - Other Data"]

The string needed to be extracted here is First Field only .. That's to remove HTT " and remove also the space before the dash - and remove the following - - Other Data"
So the final out put would be in column A : First Field

The second string to be extracted would be the last line .. as it is
So as in the example it would be Second Field

Thanks advanced for help

[YasserKhalil]

I have searched and could figure out part of the problem in this way


Sub ReadTextFile()
Dim x As Variant
Dim myFile As String
Dim text As String
Dim textline As String


myFile = Application.GetOpenFilename()
Open myFile For Input As #1


Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop


Close #1


x = Split(text, "HTT ")
Range("A2").Value = Replace(Split(Split(x(1), vbLf)(0), " - - ")(0), """", "")
Range("B2").Value = Trim(Split(x(1), vbLf)(2))
End Sub


But this code deals with one text file only and I need to deal with multiple text files

[snb]

sub M_snb()
sn=split(createobject("scripting.filesystemobject").opentextfile("G:\OF\example.txt").readall,vbcrlf)
range("A2:B2")=array(split(split(sn(0),"HTT """)(1)," -")(0),trim(sn(1)))
end sub

There are tons of threads dealing with reading several .txt files in a directory.

[YasserKhalil]

Thanks a lot for reply
I have solved the first part after a lot of search and you provide another perfect solution for it .. But after search I can't figure out the second point which is to loop through the existing files and put the results in rows
Thanks anyway for your solution

[YasserKhalil]

Please help me in that point which is loop through the text files in the folder that will be selected ...

[mdmackillop]

Sub Test2()
pth = GetFolder("C:\") & "\"
fpth = pth & "*.txt"
arrlist = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & fpth & " /b /a-d").stdout.readall, vbCrLf), ".")
For Each a In arrlist
Text = ""
LastLine = ""
Open pth & a For Input As #1
Do
Line Input #1, textline
Text = Text & textline
Loop Until EOF(1)
On Error Resume Next
txt = Trim(Split(Split(Text, "HTT " & Chr(34))(1), "-")(0))
If Err.Number = 0 Then
LastLine = Split(Text, Chr(10))(UBound(Split(Text, Chr(10))))
Range("A2").Offset(i).Value = txt
Range("B2").Offset(i).Value = LastLine
i = i + 1
End If
On Error GoTo 0
Close #1
Next
End Sub




Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

[YasserKhalil]

You're a legend. That's exactly what I was searching for
Thank you very very much for great help