Get Data from Multiple Closed Workbooks

Hi,
I have a number of directories that I have excel workbooks in. Each workbook is only one sheet, all sheets have a similar name all would be "Don't Unhide Motor Report" followed by numbers from one to 15 and shown on the tab as "(1)", "(2)" etc. to "(15)". See attachment MR CLR650-02 PO#13CJ-122-41683.xlsx

Now the code below sort of does what I would like it to, I would like it to name all the Workbooks in each directory in column A and then put Cell D6 in Column B. the results of O1 in Column C, the results of O2 in Column D, the results of O3 in Column E.
Currently it creates a new workbook, then grabs the name of workbook, puts it in Column A then takes D6 from "Sheet1" and puts in Column B, but my sheets are not named sheet1, but when it is it works good.
Another issue I guess I should mention is my directory would be "L:\Team Folders\Operational\Directional\Clear Operations\Motor Reports\Clear\6.5\CLR650-02". this is the last level but there are also Workbooks in "L:\Team Folders\Operational\Directional\Clear Operations\Motor Reports\" but the "Motor Reports" directory has 11 directories and some of them have multiple directories as well.

Please Login or Register  to view this content.

Here is workbook that the Macro is in accurals.xlsm

Thread closed at OP's request, they will re-post in the CF forum