automate the process of creating an embedded Excel file

Question

Excel 2019

I have about 5 identical files with the same data structure within them

Each file has about 20 sheets - the sheets have the same data structure

In each sheet, in the top row there are a number of informants about the content of the sheet (group name, identifying details of the group, as well as cells containing calculations of data from the sheet itself (sum of column, etc.)

I created an embedded file that takes the top rows of all the sheets in all the files and create one sheet from them (that the data will remain linked)

Basically create a file that has a table with the names of all the groups and a concentration to all the data calculated from all the sheets

I know how to do it manually

Is it possible to automate the process?
So I will not have to enter all the links manually in the embedded file?

an automated process at the sheet level at the file level

Regards
Ori