Automate importing of TXT files into Excel.

[stereoqt]

Good night! I'd like your help with the following problem. I need to import text files that are inside several folders on multiple Excel sheets. The file structure is as follows:
E:\Users
E:\Users\(user_name1)\(item_name1).txt
E:\Users\(user_name1)\(item_name2).txt
E:\Users\(user_name1)\(item_name3).txt
...
E:\Users\(user_name2)\(item_name1).txt
E:\Users\(user_name2)\(item_name2).txt
E:\Users\(user_name2)\(item_name3).txt
E:\Users\(user_name2)\(item_name4).txt
E:\Users\(user_name2)\(item_name5).txt
...
E:\Users\(user_name3)\(item_name1).txt
E:\Users\(user_name3)\(item_name2).txt
...
*All item names are different, there are no duplicates.

- The structure of txt files is super simple:
(X) item.
X equals the number of items.

- Each (user_name) must be in its own Excel sheet.

I have achieved this using Power Query (Import from folder), the problem is that I must do it MANUALLY, one by one. I have almost 200 user names (with 50-600 txt files in their respective folders), so this would take me A LOT OF TIME.
Is there any way to automate this process?

Example:

Libro1
	A	B	C	D
1	Source.Name	Column1	Column2
2	record_1.txt	0	items
3	record_2.txt	0	items
4	record_3.txt	1	items
5	record_4.txt	0	items
6	record_5.txt	1	items
7
example_1

 

[Joe4]

There are a bunch of similar threads here.

Here are a few:

VBA looping through directory structure

How do I write a code that will loop through a directory structure? I have the following problem: in a directory C:\Documents\Means\ I have directories C:\Documents\Means\Measurement 2\ to C:\Documents\Means\Measurement 120\ each of these directories consists of .txt files. In each folder...

www.mrexcel.com

Loop through subfolders - open PDFs

Hello, I am exporting some PDFs that I am then importing into Excel to do some data cleaning (removing headers, subtitles, etc.) When I do the export from Bloomberg, I am getting a subfolder for each PDF document. So I would want to be able to run through all the subfolders and do my data...

www.mrexcel.com