ExcelBanter - Extracting information from more than 1 text files

Junaid,

If your files are _exactly_ the same as what you posted, then copy all the code into a codemodule of
an otherwise blank workbook, and run the macro ReadDataFromFiles.

HTH,
Bernie
MS Excel MVP

Option Explicit
Dim myVals(1 To 7) As String

Sub ReadDataFromFiles()
Dim i As Integer
Dim FileArray As Variant
Dim myFilename As String
FileArray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileArray) Then
For i = LBound(FileArray) To UBound(FileArray)
myFilename = FileArray(i)
ReadFileData myFilename
Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 7).Value = myVals
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub

Function ReadFileData(FileName As String)
'Dimension Variables
Dim ResultStr As String
Dim FileNum As Integer
Dim Counter As Double

FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False
'Read first line
Line Input #FileNum, ResultStr
myVals(1) = Split(ResultStr, " ")(1)
Line Input #FileNum, ResultStr
myVals(2) = Split(ResultStr, " ")(4)
Line Input #FileNum, ResultStr
myVals(3) = Split(ResultStr, " ")(3)
Line Input #FileNum, ResultStr
Line Input #FileNum, ResultStr
myVals(4) = Split(ResultStr, " ")(4)
Line Input #FileNum, ResultStr
myVals(5) = Split(ResultStr, " ")(4)
Line Input #FileNum, ResultStr
Line Input #FileNum, ResultStr
myVals(6) = Mid(ResultStr, InStr(1, ResultStr, ": ") + 2, Len(ResultStr))
Line Input #FileNum, ResultStr
myVals(7) = Mid(ResultStr, InStr(1, ResultStr, ": ") + 2, Len(ResultStr))

Close
End Function

"Telecommm" wrote in message
...
Hi All,
I daily save 10 to 20 text files which contain the following info:

Site XXXXXXX installed:
Master Cab Serial No: SWxxxxxxx
Master Cab PartNo: X7xxxxxxx

Slave Cab Serial No: Swxxxxxxx
Slave Cab Part No: Swxxxxxxxx

Issues: rectifier is faulty.
Team : workerA and WOrkerB (Names)

I want to make a macro which when run, open the files one by one, extracts
the Site Name, serial number, part number and issue from the file and save in
my Excel Sheet. in C1,D1,E1,F1 etc.

Site Name can be of different length but part number is 7 digits long and
serial number is 10 digits, ofcourse issues can be of any lenght.
WOuld you please spare some time and prepare code for this.
I shall be grateful to you.
Regards,
Junaid