Hello,

I have a Workbook (it is called "Results.xlsm" and it is located at "C:\MyDocuments\") in which I want to collect (read: copy) data from specific cells of other workbooks. These other workbooks are all located within different (sub)folders of the same directory "C:\MyDocuments\". All those workbooks have one thing in common and that is that their description always contains the word "Test". All these workbooks are protected with a password. The password is "TestTest".

I'm trying to come up with a VBA-code that does the following. The VBA-code has to search in all the (sub)folders of the directory "C:\MyDocuments\" and has to find all the workbooks that have the word "Test" in their description. Then, one by one, the VBA-code has to:
  • open the workbook;
    unlock the workbook with the password;
    copy the data of cell "A1" from the sheet called "Sheet1" of that workbook;
    paste this data into cell "A1" from the sheet "Results" in the workbook "Results.xlsm";
    paste the full name of the workbook where the data has been copied from in cell "B1" from the sheet "Results" in the workbook "Results.xlsm"
    lock the workbook again with the same password; and
    close that same workbook.

The VBA-code will then open the second workbook that meets the criteria, repeats the same steps and will copy the data in the workbook "Results.xlsm" in the cells below the ones that already contain data (e.g. A2 and B2, when data is copied from the second workbook that meets the criteria). This will continue, one by one, until the data in cell A1 from every workbook with the word "Test" has been copied to the workbook "Results.xlsm". So if 100 workbooks meet the criteria, than the range A1:B100 will be filled with data in the workbook "Results.xlsm".

Is this even possible to achieve? I hope someone can help me with this.