Get Folder path, List filenames inside and open & copy data

Hi,

I am creating a new excel file where I need to get the folder path directory and put it in the cell 1. Then open that folder location and list all the files seen in that folder in the next cells. Then open each listed files and copy the data paste it in the new worksheet of the working file.

Do someone here have these codes ? Links or reference with the same code of this I am looking for.

Thanks.

different approach.