Extracting data from multiple workbooks into one

henke_a_

New Member
Joined
Dec 19, 2018
Messages
2
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,126
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_

New Member
Joined
Dec 19, 2018
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,408
Messages
5,414,228
Members
403,520
Latest member
Pineappleman

This Week's Hot Topics

Top