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[B] 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!