Extracting data from a text file

[lamore48]

Hi All,
I am new to VBA programming although I used to program in VBScript on web applications. Anyway with some help from other forums I have some code that extracts data from a flat text file. This file has a header line and a footer line and there are 2 lines of data per employee. The first line ends in 5 and the second line ends in 4. I will post the code I have so far and hope someone can help me finish it.....

Please Login or Register  to view this content.

OK, what I need to do is filter out specific trades for example I would like to show every trade except "Machine Repair" I will include a couple records from the flat file.....


HDR20120710
00101000036617 5270012008085197804171984102919730621DOE BJ52702B 2571370000000001600 5
00101000036617JOHN 109080 55512345671952093000000001MACHINE REPAIR 4
00101000099788 5270002010384198910301989103019891030SMITH DS52501C 257077S000000000000 5
00101000099788BOB 109109 55523456781952082700000001ELECTICIAN-PROJECT COORD 4
00101000099920 5270002011522199103181991031819901001BLUE SJ54302B 2570770001600000000 5
00101000099920STEVEN 109109 55534567891960050400000001ELECTRICIAN 4
00101000111236 5350601002558 1984102919740815ROAST DJ53502A 5904110000000000000 5
00101000111236CHUCK 109109 55545678901952102800000001ENGINE ASSEMBLY ASSOC


OK, above are 4 records.(don't forget it begins with a header line) so my code disregards that, but I try filter out "Machine Repair" and it doesn't work. I don't know what I'm doing wrong.

Thanks in advance,
Lenny

[protonLeah]

how about:

Please Login or Register  to view this content.

[lamore48]

Hi Ben,
I ran your code and it skips the header line and moves to the first line of data. It gets to the second line(Which the first 2 lines are the data for the complete first record. i.e. For every employee there are 2 lines of the flat file which contains all the information about that employee. Anyway it gets to the second line of the data and it puts everything into each cell except "Machine Repair". Meaning it is still putting in the last name, first name, senority date, and department of the "Machine Repair". What I need it to do is if it is in fact a "Machine Repair" trade then to skip that whole record and move on to the next one. I hope I am explaining this right.

Thanks,

Lenny

[protonLeah]

Try this one:

Please Login or Register  to view this content.

[lamore48]

Ben that code worked great! Now the next question I have is: We excluded the "Machine Repair" trade, how would we change the code to ONLY include the "Machine Repair" trade and exclude everyone else?


Thanks,
Lenny

[protonLeah]

The InStr function returns zero if the sought string is not found and greater than zero if found. In the macro, we checked for a zero, indicating that it was not found and proceeded to process the record.

If the line were: If InStr(strLine2, "MACHINE REPAIR") > 0 , the lines following would only be executed if "Machine Repair" were found; all others would be ignored.

[lamore48]

OK, So now I'm getting a little brave and am trying to sort and put each trade into a separate sheet, so far I only coded "Machine Repair" and "Electrician", anyway the following code adds a sheet called Machine Repair and closes and stops, if I step into the code it goes through the flat file gets all the "Machine Repair" and then when I comment out the line "On Error GoTo CloseIt:" I get an input past end of file error.... here is the code

Please Login or Register  to view this content.

Thanks,

Lenny

[Cutter]

@lamore48 Welcome to the forum

Please notice that code tags have been added to your posts. The forum rules (Rule #3) require them so please keep that in mind and add them yourself whenever showing code in any of your future posts.
Thanks.

[protonLeah]

This macro will filter the trades to their own sheets:

Please Login or Register  to view this content.