Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting information from more than 1 text files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting information from more than 1 text files
Thanks Dear. It works fine for me. Thanks for the support. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from large text files for beginner | Excel Worksheet Functions | |||
Extracting information | Excel Worksheet Functions | |||
Extracting certain information from cells | Excel Worksheet Functions | |||
extracting information from workbook to another | Excel Programming | |||
Excel and Text Files - Extracting | Excel Programming |