Working With Many Worksheets

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:

• wildcard characters ? and * in filenames (e.g. c:\my\folder\*.xlsx for all files in my\folder); and
• multiple folders or files separated by ; (e.g. c:\2018\report.xlsx;c:\2019\report.xlsx)

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:

1. replace WSheet Use for the only source worksheet by WBook List Start for all the worksheets at the beginning of the process (NB: keep WSheet Use for the target!);
2. specify all the worksheets in the Workbook list parameter
3. add WBook List Next to the end of the operations;
4. use the Djeeni name of WBook List Start as the source worksheet reference in all process steps instead of the Djeeni name of WSheet Use for the only source worksheet
5. replace the target cell of cell and range operations to allow for
   • appending rows;
   • inserting rows; and
   • accumulating values instead of overwriting the same cell again and again. See these steps detailed at the sections on this page below.

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:

• If Range Copy/Move is used then the only change is to set the Insert / Overwrite parameter to Insert.
• If Cell Set or Range Set is used then this process step must be preceded by Insert Column/Row inserting one row before row 4.

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:

1. If the number of target worksheets is fixed then they can be specified in a WBook List;
2. If the target worksheets should be created using a supplementary list (for example cost center or department list) then this supplementary list can be used in a Row List or Column List
3. If the target worksheets are to be created using the information from the source worksheet itself then the WSheet Split process step can be used

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:

1. WSheet Use for the source worksheet
2. WSheet Use for the supplementary list
3. Optionally create the target workbook (using WBook New) in case all the target worksheets will be added into one workbook
4. Row List Start on the supplementary worksheet
5. Creating the next target workbook (WBook New) in case the target worksheets will be added to different workbooks OR adding the next worksheet to the before the Row List created target workbook;
6. performing the operations (mostly filtering the source worksheet and extracting the filtered data) using
   1. the Djeeni name of the Row List to refer to the information on the supplementary list
   2. the Djeeni name of the source worksheet to refer to the source data set
   3. the Djeeni name of the created/added worksheet to refer to the target data set
7. Row List Next to get to the next element of the supplementary list

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:

1. Find the first cell of the range using Cell Lookup
2. Find the last cell (or the cell after the last cell) of the range using Cell Lookup
3. Specify the range using Range Use and the two cells

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