I need an excel macro that will do the following:

• ask you for a path
• will look into the path and its subfolders and find all xls/xlsx files that have name ends with "BOM", "these bom files is saved using solidworks same template so they have identical headers.
• then it will copy the content of all files into one sheet, first row is header, and first column "A" will have the  source file name with path, and second column "B" will ask you for qty per item (xls file), the remaining data are the copied data from files.

the purpose of this macro is to have grand BOM for many items so I can use pivot table to combine similar items.

please see screen shots below