Extracting information from more than 1 text files

[Telecommm]

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

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Telecommm]

Thanks Dear.
It works fine for me.
Thanks for the support.