Extracting data from multiple workbooks into one

[henke_a_]

Hi,
First off, I am a complete newbie at macros. I have found several threads on this topic but can't seem to get it right still...

Background: A survey with 40ish groups (each containing 5 or so persons) have been performed. I want to gather the answers and respondants to all the questions in 1 excel-file.

Problem: I have one folder for each team (named after the team). In each team folder, the structure is the exact same. There are several subfolders (8 of which are relevant). Each of these subfolders contains several different folders (one for each question). In each of these folders, there are 2 excel-files that are of interest. One named "meta" and one named "data". "Meta" contains the question, and "data" the answers. The name of the question in "meta" is located in B2. The responses in "data" is located in D2-X (X depending on the number of responses). The respondants are located in "data" H2-X (X depending on the number of responses).

I would like to do the following:
1. Extract the questions from each "meta" file and put them each in row 1, starting from B1.
2. Extract the respondants from each "data" file and put them in column 1, starting from A2.
3. Extract the answers from each "data" file and put them in rows, starting from B2 (the answers need to correspond to the correct respondant and question)

I appreciate all the help I can get. All the best!

 

[John_w]

Welcome to MrExcel forums. With your folder structure (folders within folders within folders), you want a recursive procedure which uses FileSystemObject (FSO) to loop through the folders and files and open the workbook if the file name is "meta" or "data". Are these workbooks .xls or .xlsx files or something else? Search the forum for the basic FSO folder looping code and there should be examples.

 

[henke_a_]

Welcome to MrExcel forums. With your folder structure (folders within folders within folders), you want a recursive procedure which uses FileSystemObject (FSO) to loop through the folders and files and open the workbook if the file name is "meta" or "data". Are these workbooks .xls or .xlsx files or something else? Search the forum for the basic FSO folder looping code and there should be examples.

Alright I will have a look thanks. The workbooks are .xlsx files.