Import Specific line from multiple text files

Hi,

I have many text files ~5000 and I am searching for a macro that can import multiple files and also search the text file only importing two specific rows (error files may will not have 42 rows) while listing the file name in an adjacent cell.

So in column A I would like the file name, in column B any data in line 42 of the text file and in column C any data in line 43 of the text file.

Here is an example of the data in rows 42 & 43:
11 Waratah Street Mona Vale(2103) - Australie
-33.68 (-33°40') | 151.30 (151°18')

So far I have found the code below that will import all text files into one sheet but it does not satisfy my requirements.

Is what I’m after possible?

Please Login or Register  to view this content.

Did you write this code? (I ask because it seems you should be able to complete it if you did.)

You could use Split instead of the loop for parsing the line at sep.

I wish! No I have been just searching the net for code and found this one on some website. Unfortunately I have fairly low VBA skills and can only make small simple changes to code I find on the net...this problem is beyond my skills

Hi
Try the following macro.

Please Login or Register  to view this content.

Ravi

Thanks but that macro just lists the file names

I had to remove "Destination:=.Range" the period between equals and range
and I also had to remove ".Refresh BackgroundQuery:=False" to get this macro to run.

Please Login or Register  to view this content.

Hi
The code is supposed to import text file into col AA and pick 42nd and 43rd line from there and delete earlier data before next file is called. It copies AA to AZ and pastes it against their file name.
Ravi

Sorry but it doesn't work.

Hello Vlad999,

This macro use the FileScriptingObject. Since the FSO is created at runtime, you don't need to add the library reference to your project to make it work. The macro checks that each file exists, and has at least 43 lines in it. It starts reading the file names at "A2" of the active sheet. You change this in the macro (look for the red code). The next column to the right holds line 42, and the next cell to right of that one holds line 43. Copy this code into a Standard VBA module.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thank you. the explaination of how it works was also very useful thanks again.

Hello Vlad999,

Did the code work as you requested?

Sincerely,
Leith Ross