Copying specific fields of multiple txt files into one master excel

[thomasccw]

Dear experts of the forum,

I would like to seek your kind help in creating a master excel (Compiled Data), which is able to copy specific fields of multiple txt files automatically. There are three main files I am working with:

(1) Compiled Data.xsl:


First row [column in sq brackets]
- SubID[A] Treatmt GT(min)[C] GT(days)[D] Prog[E]


(2) ####_GameSummary.txt


Copy last field of Column A into Column A of (1)
Copy last field of Column B into Column B of (1)
Copy last field of Column M into Column D of (1)



(3) ####_RawData.txt


Copy last field of Column F into Column C of (1)
Copy last field of Column H into Column E of (1)

-


Notes:
- #### is the SubID in number format, it may have three values (e.g., 105) or four values (e.g., 1709). Each SubID has two files namely GameSummary.txt and RawData.txt. For example, 1709_GameSummary.txt and 1709_RawData, I would need this to be copied into one single row in Compiled Data.xsl. Preferably arranged from smallest to largest number by SubID (Column A of Compiled Data). It would be great if the macro can run every time Compiled Data.xsl is opened, detecting any changes in the folder automatically.

- The txt files are column delimited and import to excel nicely so there is no need to do adjustments.


- All files are in the same folder.


Do kindly let me know if anything is unclear. Hope to hear from you guys soon. Thank you in advance!

 

[thomasccw]

Any help is greatly appreciated!