4.23 Aggregate files - aggregate by cell positions - unfixed number of files

 

The head office receives the balance sheets from each branch, one of the tables is shown below (there are 37 rows in total, but only 14 rows are shown in the table):

imagepng

Now we want to aggregate these balance sheets to generate the balance sheet of head office.

Script:

A B C
1 =directory@p(“zc*.xlsx”)
2 =A1.(file(~).xlsopen())
3 =to(4,37) [B,C,E,F] =A3.(B3.(~/A3.~)).conj()
4 for C3 >v=null
5 for A2 >v+=number(B5.xlscell(A4,1))
6 >A2(1).xlscell(A4,1;string(v))
7 =file(“total.xlsx”).xlswrite(A2(1))

A1: List all the to-be-aggregated balance sheets whose file names begin with zc in the folder, and @p option means listing the full path of the file

A2: Open the files listed in A1 as Excel objects

A3: Specify the row number range of to-be-aggregated numeric cells: 4-37

B3: Specify the column numbers of to-be-aggregated numeric cells: B,C,E,F

C3: Concatenate the row numbers in A3 and column numbers in B3 to form the name for every to-be-aggregated numeric cell

A4: Loop through all to-be-aggregated numeric cells in C3

B4:  Define the aggregation value variable v

B5: Loop through the balance sheet of every branch

C5: Read the value of current aggregation cell from the balance sheet of current branch, convert it to a number and add it to v

B6: Save the added v to the balance sheet of the first branch

A7: Save the balance sheet of the first branch to the balance sheet of head office total.xlsx