VBA for listing Products in All Excel Files in Folder

Hi Excel Gurus.
In my new job encountered with a challenge. The solution of my problem will save my time. However I am pretty good at excel functions but I am sure that this problem cannot be solved with functions. Thus asking for your help. Appreciate all kind of suggestions and help.

My partners archived more than 1000 excel files in one folder and each of these folders contains Product names with its prices. All these products start at cell F7 and its numbers changes. Some of them only one and some more than 15. They are in the same row. F7-G7-H7 etc.

Yesterday I was able to find VBA code to list only file names in folder. But Its not enough unfortunately. I attach the file with the Folder picture. In "My VBA" sheet there is macro that I found. But for me the most important is "Improved Ver." sheet. And there I simulated the desired result. In one column I want to list products and there file names in front of them.

In my real data there more than 1000 excel files (all of them in 2003 vers.) and each of them contains 1 to 20 (or more) products. In the end there will be about 10000 row listed. I use Excel 2007.

Thank you in advance

Hi mrice
Thank you for quick reply.
But I couldnt make it work. Is it possible to select folder with FileDialog(msoFileDialogFolderPicker) method.
I replaced real path with "C:\temp\" but got no result.

Yesterday I couldnt make it work. After a closer look to your codes I noticed that:
ThisWorkbook.Sheets(1).Cells(X, 6) = ThisWorkbook.Sheets(1).Cells(N, 1) in this row there should be letter "M" instead of "N".
After that correction macro started to work. It list all products name but it doesnt list its file names. I add the file.

Thank you again

Its wonderful. Thank you very much. It works perfectly.
Now I have to test it in work. Hope it will work there, too.

Thank you again. Good luck