Loop through all Sub Folders and copy data in .txt files into individual tabs

[_eNVy_]

Hi all,

Quickly : There are folders for each month of the calendar year and in those month's folders are sub-folders which belong to various companies. In the company's folder are more folders which holds .txt files that I need the the contents to be extracted from.

Essentially, in folder "03 March" there is a folder for company "Company Excel" and inside that company folder is the Workbook "03 March 2018 Company Excel.xlsm". This .xlsm file will look at all sub-folders within "Company Excel" folder (in "03 March") and copy the contents of all .txt files and paste it in their own individual tabs (with the tabs being labelled with the .txt filename). Within "03 March 2018 Company Excel" there is a tab calld "Master" which will remain blank.

Could the macro possibly not be directory specific? As going forward, I believe it would be best to copy the previous months' .xlsm file for the next month and would like to avoid having to rename the filepath in the code.

When copying the contents of the .txt files, could the name of the .txt files be put against the pasted contents?
Just as a rough idea, some .txt files contents could range from 0 - over 1,000 lines.



As a side note: there are folders called "Unsuccessful" which has .pdf files. I would like for their filenames to be pasted in a new tab with the same name as the folder. There are also .pdf files in the date folders (mentioned above) which I would like their filenames to be pasted in a tab called "Successful". (Maybe it would be easier to have those .pdf files in a folder called "Successful"?)

I think is a big ask so thank you in advance!!!

 

[ravishankar]

Hi download the file from https://drive.google.com/open?id=1yY...SDoorqWRCjHji2 , save it in folder containing your text files. Goto macro and change *.xls* to *.txt and run the macro. it should list all file names and its contents from the folder where it is saved. If it works, let us see how it can be extended to other directories.
Ravi shankar

 

[_eNVy_]

Hi download the file from https://drive.google.com/open?id=1yY...SDoorqWRCjHji2 , save it in folder containing your text files. Goto macro and change *.xls* to *.txt and run the macro. it should list all file names and its contents from the folder where it is saved. If it works, let us see how it can be extended to other directories.
Ravi shankar

Thanks Ravi however, I need something that goes inside each subfolder.

Maybe I need to re-visit how this is done in order to attain a proper functioning file with an easier output.