Loading Data automatically from several .txt files into different sheets of an .xls

[lookingforananswer]

Hello everyone,



I have the following problem:



I´m trying to analyze several measurements in one excel Worksheet.

Therefor I did several measurements, which resultet in a single .txt (tabulator seperated) file for each measurement.

If I drag and drop (copy paste works too) the .txt in an excel sheet, everything works fine but it is a lot of work for the amount of different measurements, so I would like to automatize the process.

The xls. file consists of the main sheet with all the diagrams and statistics which use the data from the measurements that are stored in seperate sheets in the same .xls file.

My main idea is, that on the overview sheet I provide the name of the sheet which should contain the measurement and the file path of the corresponding .txt file of that measurement and excel should automatically load the data from that .txt file in that sheet, as if I had done it by hand via drag and drop.


How can I realize that?

In the picture I have showed the basic structure of the file system and the .xls with the different sheets for each measurement .txt file.

[macropod]

Excel can't 'automatically' load files via formulae; you'd need a macro to automate the process. Such a macro could be made to work with the text you have in the columns, though you really don't need anything more than the text file's name (i.e. without the path & extension on every line) and you'd also need to put some code into the macro that would specify where on each sheet the data are to do.