Naming and using a worksheet is a very useful concept in case of a few, known worksheets. There are cases, though, when several or many worksheets with the same structure must be batch processed or created. Typical cases are consolidation or splitting tasks.
The source worksheets that should be consolidated do already exist having their filenames at hand. Therefore best practice is to use the WBook List Start and WBook List Next process steps (under categories Worksheet and If / Lists on the ProcessToolbar) to perform the same operations on multiple source worksheets.
Under the workbook list parameter the workbooks of the source worksheets can be specified using:
The first step is to specify the process for one source worksheet just like it is done for a single worksheet case. Use WSheet Use to specify the target consolidation worksheet and another WSheet Use for the only source worksheet. Once the process is up and running follow these steps:
Done!
The single worksheet version of the consolidation process copied some (processed) data from the source worksheet to a certain cell/range of the target consolidation worksheet. And in the consolidation template, there is empty space to do this for all worksheets by using the same copy operation to append data. But how to find the first empty row/cell? The first empty row/cell is, of course, one row/cell further (below or right) then the last row/cell with data. The last row with data is easy to declare for column A:
#RowEnd
and for another column (e.g. F):
#RowEnd|F
Adding one to the above (to get to the first empty row) is:
[+[#RowEnd|F]+1]
Let's assume that the single worksheet version copied (by Cell Set or Range Set or Range Copy/Move) the data to wsConsolidation!D2. To append the rows below this should be changed to wsConsolidation!D[+[#RowEnd|D]+1].
Another way to build the consolidation list (in case the template has only a header and no fixed structure) is to insert the new rows from the source worksheets before the existing ones. There is no need to calculate the first empty row.
Let's assume that the consolidation list starts at row 3 of the wsConsolidation worksheet. These are the changes in the data processing steps to make the consolidation work for multiple worksheets:
Consolidation is on one side collecting values in lists. On the other side, it is accumulating (summing, averaging etc.) values in a cell on the target worksheet. At the single worksheet version it is accomplished by setting the target cell value using Cell Set. And voila! The multi-worksheet version leaves Cell Set in place. The only change is to use the previous cell value and the needed calculation or Excel function to produce the new value.
Example 1: The sum of source values should be calculated.
'Single version Cell Set Cell: wsTarget!C4 Value: [=wsSource!D5] 'List version Wbook List Start ... Cell Set Cell: wsTarget!C4 Value: [+[=wsTarget!C4]+[=wsSource!D5]] ... WBook List Next
Example 2: The average of source values should be calculated. Here we need a supporting cell to count the number of values to be averaged. This value we set before the Wbook List to 0 and after the Wbook List is ready we divide the summed up value with the final number.
'Single version Cell Set Cell: wsTarget!C5 'number of values Value: 1 Cell Set Cell: wsTarget!C4 'average of the only cell Value: [=wsSource!D5] 'List version Cell Set Cell: wsTarget!C5 'initializing Value: 0 WBook List Start ... Cell Set Cell: wsTarget!C5 'increasing number of values Value: [+[=wsTarget!C5]+1] Cell Set Cell: wsTarget!C4 'summing only Value: [+[=wsTarget!C4]+[=wsSource!D5]] ... WBook List Next Cell Set Cell: wsTarget!C4 'now to calculate the average Value: [+[=wsTarget!C4]/[=wsTarget!C5]]
A typical task is to split an MS Excel source worksheet from an external source (it can be e.g. a supplier provided data set or an IT system report) according to certain criteria. The question is, how it is decided which target worksheets should be created? They do not exist before the process so the filenames are not known. Further, the set of the to be created files can be different every time based on source or supplementary info. Djeeni can create target worksheets in three ways:
Let's see these ways in details.
This approach is similar to consolidation. The target worksheets (and -if needed- the corresponding workbooks) will be created and processed one by one. The current worksheet can be referenced by the Djeeni name of the WBook List.
Often, processing of a source data set involves a supplementary list of some master data. They are mostly list of cost centers, departments, contacts, partners. The process should extract the relevant part from the source worksheet for each element in this supplementary list. In this case, the processing of the source worksheet is governed by the supplementary list. Djeeni uses the Row List Start … Row List Next process steps to take the elements of the supplementary list one by one and perform the corresponding operations. The actual steps are:
These steps make the following Djeeni process (for different workbooks). Note how the target workbooks are uniquely named using the supplementary list information in WBook New.
WSheet Use Djeeni name: wsSourceData 'source data set WSheet Use Djeeni name: wsDepartments 'supplementary list Row List Start Djeeni name: rlDepts Worksheet: wsDepartments Row from: 2 Row to: #RowEnd|C WBook New Djeeni name: wsDepTarget Filename: [=rlDepts!D#] 'dept. name in column D of suppl. list Type: Target Worksheet Excel name: Report 2020 June ... 'operations for the current target worksheet referenced by wsDepTarget Row List Next
Once the process structure to create the target worksheets is ready the corresponding parts of the source data set should be selected for each target worksheet. In case the selection should be done using a known value (known constant values or values coming from a supplementary source using Row List) in a column of the source worksheet, filtering is the best practice. Djeeni implements the MS Excel autofilter (known as the dropdown boxes at every column header) using the WSheet Filter Add and WSheet Filter Clear process steps.
If multiple WSheet Filter Add is used on a worksheet then they are combined (as AND) and only the rows that match both filter criteria will be selected. Every Range and Cell operation works with the filtered data set.
Example 1: Fix (small) number of target workbooks and known filtering values
WSheet Use Djeeni name: wsSourceData WBook List Start Djeeni name: wlTarget Workbook list: c:\wbook1.xlsx;c:\reports\wbook2.xlsx If Condition: [#wlTarget|NAME]=wbook1 WSheet Filter Add Worksheet: wsSourceData Column: D Criteria 1: 1500 ... 'operations for the first target worksheet referenced by wlTarget WSheet Filter Clear End If If Condition: [#wlTarget|NAME]=wbook2 WSheet Filter Add Worksheet: wsSourceData Column: E Criteria 1: UK ... 'operations for the second target worksheet referenced by wlTarget WSheet Filter Clear End If WBook List Next
Example 2: Split source using a supplementary list of departments. Note how the target workbooks are uniquely named using the supplementary list information in WBook New.
WSheet Use Djeeni name: wsSourceData 'source data set WSheet Use Djeeni name: wsDepartments 'supplementary list Row List Start Djeeni name: rlDepts Worksheet: wsDepartments Row from: 2 Row to: #RowEnd|C WBook New Djeeni name: wsDepTarget Filename: [=rlDepts!D#] 'dept. name in column D of suppl. list Type: Target Worksheet Excel name: Report 2020 June WSheet Filter Add Worksheet: wsSourceData Column: D 'dept. ID in column D of the source data set Criteria: [=rlDepts!F#] 'dept. ID in column F of suppl. list ... 'operations for the current target worksheet referenced by wsDepTarget WSheet Filter Clear Row List Next
Certain source data sets contain ranges that are identified by a start value and an end value. For example, a range of one month that starts on the first of the month and lasts till the first of the next month. In this case, the range can be identified by the two cells that can be found using Cell Lookup. The steps are:
WSheet Use Djeeni name: wsData Cell Lookup Djeeni name: ceFirst Value: 1-11-2020 'first of November Range: wsData!C1:C20000 Cell Lookup Djeeni name: ceAfterLast Value: 1-12-2020 'first of December Range wsData!C1:C20000 Range Use Djeeni name: rgNovember Range: wsData!C[#ceFirst|row]:C[+[#ceAfterLast|row]-1]
Workbook lists have their own Djeeni names. These Djeeni names behave like the Djeeni name of a worksheet specified in WSheet Use. You can use the two Djeeni names interchangeably where appropriate.
WSheet Use Djeeni name: wsTarget Cell Set Cell: wsTarget!C3 WBook List Start Djeeni name: wlTarget Cell Set Cell: wlTarget!C3
At row lists, there is always a current row that is referred by # instead of an actual row number. In case of nested row lists, # can be extended by the Djeeni name of the Row List.
WSheet Use Djeeni name: wsOuter WSheet Use Djeeni name: wsInner Row List Start Djeeni name: rlOuter Worksheet: wsOuter Row List Start Djeeni name: rlInner Worksheet: wsInner Cell Set Cell: wsOuter!D[#|rlOuter] 'current row of the rlOuter row list Cell Set Cell: wsInner!D# 'the nearest (innermost) row list