Batch copy and transpose multiple CVS files into a single summary Excel sheet

Question

Hi All,

This is my first question at this forum because I come across a real head scratcher..

I am trying to adapt a VB code, published at https://www.extendoffice.com/documents/excel/3388-excel-import-multiple-text-csv-xml-files.html#a2 to import multiple CVS files from the same directory and place them transposed into a single Excel worksheet.

The source files are all located in the same folder.
There are 3 columns of data in the source files, but only 1 column need to be imported,
The imported data is to be transposed and placed in the next empty row on the target worksheet.

My modified code is so messy that I do not even want to paste it here,but you can find two sample input CVS files and a desired output XLSX file attached.

Thanks for all help in advance.