Copy data from 40 different text file and paste to excel into column wise

[Sridhar Sukumar]

Hi VBA experts,
Seeing your support on the below steps to automate.

I have nearly 40 *.dat files having different results.
*.dat file can be opened in notepad or Text pad.
Since Chandoo.org policy *.dat cannot be attached, so I attached as a *.txt file.

Manual process :
1. I have 40 result files, for example in the name of File1, File2, File3, File4 .,
2. Open File1.dat file in Text pad application and search for a DAMPING RATIO word and go to the desired data location to copy.
3. Then copy the data using the block selector option (shown in Image, double click the Block button to activate) so that specific data will be selected row-wise.

4. Then holding the shift button click the page down button to reach the end of the data files and copy it. A number of data lines vary in each file.
5. Open the Master excel file and paste the data below the File1 (B2) heading and minimize the excel.

6. Then close the File1.txt file
7. Open the next file of File2.txt in Text pad and search for a DAMPING RATIO word and go to the desired data location to copy.
8. Then copy the data using the block selector option (shown in Image, double click the Block button to activate) so that specific data will be selected row-wise.
9. Open the Master excel file and paste the data below the File2 (E2) heading.
10. Repeat the steps for remaining files too. . . .

Can someone please help me to minimize this manual work using Excel VBA.

Thanks in advance.

Regards,
Sridhar S
Don't add here email-address

 

[Marc L]

Hi,​

as Excel can't pilot 'Text Pad' but can directly import text files and filter data​

join at least the expected result workbook according to your initial attachment …​

Are you under Windows ?​

​

 

[Sridhar Sukumar]

Yes, I am under Windows.