Importing specific data from multiple text files in a file directory into excel

I have been trying to import specific data from multiple text files in a directory that updates daily. I just need a code that can go through and import the data for the last 1000 text files. I have no prior vba excel programming experience. I have been trying to find codes to manipulate for what I need but i can't get it to work. Below is a template of what the text file looks like. The important data is within the first 11 lines. I only need the number after "Pump ID:" such as "563584", the Date, the number after the "Backlash Value:" such as "786," and the "Read Back Test" result. The way I need is organized is below in the screen shot. Any help will be appreciated. I am using excel 2010.

XXXXXXX Pump Backlash Log
Pump ID: 563584 -- 2013_06_21--08-18-01
Date: Fri, Jun 21, 2013
Time: 8:20:00 AM
SW P/N = 004024
SW Rev = F
Backlash Loc: 17351
Backlash Value: 786 HEX Value = 0x028A
Backlash High Limit: 1425
Backlash Low Limit: 187
Read Back Test: PASSED
---------------------------LOG DATA----------------------------

08:18:11.938 <echo1>
08:18:12.939 [echo1] Ok.
08:18:12.940 <Version>
08:18:17.940 <Version>[Version] 35852, 35798, 35809
08:18:17.940 [MspVersion] 35852, 35798, 35809, 0, 0
08:18:17.940 <ReadpcbaID>
08:18:19.940 <ReadpcbaID>[ReadpcbaID] 87810136, 4664, A
08:18:19.940 <ReadpumpID>
08:18:21.940 <ReadpumpID>[ReadpumpID] 0, 0,


I would like it organized into excel like the screen shot below.
excel screen shot.PNG

Hi Leith,

Thanks for responding.

1)Files are all in th same folder
2)The folder path is "S:\Share\MfgMTE\BackLashLogFiles"
3)Last 1000 files are determined by the date they were uploaded to the folder

Thanks,
Ayman

So the dates can change because upto 50 or 60 files can be uploaded per day. I hope the screen shot below will explain. I base dates of the files on the "date modified" column of the folder.

folder dates.PNG

Hey Leith,

Tried the code today. But it keeps highlighting the Read Back Test line in the code in red and yellow. I included an image below. What does it mean?
code error.PNG

Hey Leith,

It works correctly. I just copied it back in fresh from the start and I have ran it multiple times with no issues. I must have messed something up the first time. Thank You so much!

Leith one more question.

I changed the code at this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

in order to get the date without the day, but now when I run the program the dates are all out of order. I can fix it by hitting sort. I tried running the macro where I sort the data and copy it into the code but it doesnt help. Is there an automatic way to do it?

Hi Leith,

Yes, I reformatted the date column to be "June 14, 2013" instead of "Fri, June 14, 2013." But the dates are still not in order. I was curious if there is coding to include in the original code procedure to get them in order automatically instead of having to go through sorting button.

Hey Leith,

I would like the dates to go from new to old and time to go from new to old also.
I played around with the code a bit. Added the time as a column. So the code looks like this:

Please Login or Register  to view this content.

and the second macro I made is below. It sorts by date and time. Combining the two codes would be awesome. I tried but it didn't work.

Please Login or Register  to view this content.

Leith,

Your Awesome. Thank You!

Ayman

Hi Leith,

I made some changes to my database and everything is working great. One issue I have is that sometimes the Pump ID values are repeated in the column with different read back test because they may have been tested once and failed then tested again and passed. How do I filter or remove the duplicates so that only the most recent record for the Pump ID remains with the most recent read back test.

The excel document is here: Automatic Data Organizer_Ascending.xlsm

Below is a screen shot of what Im talking about with the highlighted rows. Sometimes the same Pump ID can occur more than 2 times.
duplicates.PNG