Load multiple excel files into an excel sheet and extract data

Hi all,

I am new with VBA and got a question from a collegue if I could make a excel userform that could do the following:

Load multiple excel files into a excel sheet and extract data (results) from the loaded excel file.
The idea is that each excel file is loaded after the data from the previous excel file has been extracted and stored somewhere in a different sheet.

I have included a small drawing to make it more clear.

The results from each excel file are stored in sheet1 of the excel file.
The results have to be loaded into sheet1 (or any other name) of the main workbook.
After the results from each excel file have been loaded into the sheet, the applicable results (several cells) have to be copied and pasted into sheet2 of the main workbook.
Copy/pasting is to be done below the previous results that have been pasted.

Via a userform the user has to be able to select the directory where the excel files are stored.

I know that it is some kind of loop which need to be activated, loading each file into sheet1 of the main workbook, copying the data to sheet2, delete the loaded excel file and opening the next one.
But how this is done in VBA... I have no idea. If someone could help me out, especially with loading the files in sequence....

Regards,
Martijn

Tigeravatar,

Thanks for your reply and help! I copied the code you gave into VBA. I made two test files (book2 and book3) and it seems that only one file gets loaded to the main sheet. At least, the results are given for just one.
If two files where loaded correctly, the first row would show the results for the first file and the second row for the second file etc. Perhaps I made a mistake.... If you could have a look at the main file. To be complete I attach all three files.

Thanks in advance,
Martijn

Hi Tigeravatar,

I used the code, and after removing the * in between End(xlUp)*.Offset(1)*.Resize(, 3)... it worked ok .

Thanks for your help!

Regards,
Martijn

Hi Tigeravatar,

How do I make the search variable? Meaning, instead of defining the cells manually in:

Please Login or Register  to view this content.

Make is such that the user can provide a keyword string (each word is one cell and the words are located in the same row, with the first word at the left of the second word).
This keyword string has to be searched for in the opened file and the result, which is located 3 cells to the right of the last keyword, is returned to sheet2 as done in the previous code.

Example:

KEYWORD STRING: PIPELINE LENGTH (each word in a cell)
The location is unknown, so the code has to look for these words.
If for instance, the code has found a match (lets say: PIPELINE = Cell "B3" and LENGTH = Cell "B4", but this can be a different row), the result of Cell "B7" is returned and copied in the first empty row in sheet2.
If for instance, the code has found a match (lets say: PIPELINE = Cell "D5" and LENGTH = Cell "D6", the result of Cell "D9" is returned and copied in the first empty row in sheet2.

I hope I made it clear what I am looking for . I have tried to post the request in a different post, but that didn't work so if you or someone else could provide some assistance.....

Regards,
Martijn

Hi Tigeravatar,

Sorry for being inconsistent. What I meant with the second example is that the location of the result of a second keyword string (e.g. WATER - DEPTH) can be in a different cell if the same offset to the result cell is chosen.

The examples each represent a different file, with the data in different cells.

The user has to fill in the keywords in textboxes, using one textbox for each keyword. Hence, one for PIPELINE and one for LENGTH.
If two keyword strings of 2 words each, the user would have to fill in 4 textboxes etc....

See attached example I made.

Hope this clarifies my request. In the example I used the keywords WATER - DEPTH and PIPE - TENSION. However, this can be anything....
The location of the result of each keyword string would always be in the same column (constant offset to the last keyword), but could be in a different row.

Additionally, each example is presented in a different sheet, but in the code they have to be loaded (one after the other) in the same sheet.

Regards,
Martijn

Hi Tigeravatar,

Thank you very much for the code and it works great. However, and I am sorry for not being more clear (sometimes it is difficult to explain something which is in your head but you can't write it down until you see something similar working), is it possible to have the offset variable? I have more keyword strings that need to be looked through, but the offset, which is set at '3' can be different for each keyword string. At this moment both keyword strings use the same offset, relative to the last keyword of the string. Example: WATER (Cell C2) and DEPTH (Cell C3) will return the result from Cell C6 etc. But this can be, depending on the data that needs to be found, Cell C8, C5, C11 or C16 also. Hence, for each keyword string, the offset needs to be made variable (user defined via a additional textbox) or pre-defined in the code. Is this possible, or am I asking to much?

Please Login or Register  to view this content.

At this moment you use a counter (code is partly shown) to walk through each keyword string, but I presume that each keyword string can have its own offset instead of using

Please Login or Register  to view this content.

I hope you can help me once again . If I know the code for two keyword strings, each with their own offset, I can expand the code to more than two keyword strings.

Regards,
Martijn