This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
en:user_guide:worksheet_basic [2020/06/20 23:18] tiger [List of Worksheets] |
en:user_guide:worksheet_basic [2021/01/22 12:51] (current) tiger [Example: Data consolidation] |
||
---|---|---|---|
Line 3: | Line 3: | ||
The data structure of MS Excel is quite simple: | The data structure of MS Excel is quite simple: | ||
- | * Data (numbers, texts, dates) is in cells | + | * Data i.e., number, text, date values are in cells |
* Cells are organised in rows, columns or ranges | * Cells are organised in rows, columns or ranges | ||
* A worksheet contains the cells and ranges | * A worksheet contains the cells and ranges | ||
- | * Worksheets may optionally | + | * Worksheets may be grouped into one workbook or they can be found in different workbooks. |
- | Djeeni starts accessing this data structure at the worksheets. Every Djeeni process begins by identifying the worksheets that are used in the process. Worksheets are identified physically the very same way as in MS Excel: workbook location + workbook filename + worksheet name. The physical identifier of a worksheet is sometimes very long, cryptic and can change (e.g. \\serverW4Q33\corporate\finance\2020\projects\budget\exports\export15427.xlsm). Djeeni lets you assign a friendly | + | Djeeni starts accessing this data structure at the worksheets. Every Djeeni process begins by identifying the worksheets that are used in the process. Worksheets are identified physically the very same way as in MS Excel: workbook location + workbook filename + worksheet name. The physical identifier of a worksheet is sometimes very long, cryptic and can change (e.g. \\serverW4Q33\corporate\finance\2020\projects\budget\exports\export15427.xlsm). Djeeni lets you assign a friendly [[: |
===== Using worksheets ===== | ===== Using worksheets ===== | ||
- | To identify a worksheet for the Djeeni process the process step **WSheet Use** (to be found on the [[en: | + | To identify a worksheet for the Djeeni process the process step **[[en: |
- | * It is not necessary to type the physical location and filename of the containing workbook. Double click on the WSheet Use name in column A and use the Browse button | + | * It is not necessary to type the physical location and filename of the containing workbook. Double click on the WSheet Use name in column A and use the Browse button |
- | * Choose a Djeeni name that describes best the data in the worksheet. This name will be used throughout the process to access | + | * Choose a [[: |
- | * Choosing ' | + | * Choosing ' |
* Use the 'If Target then worksheet action' | * Use the 'If Target then worksheet action' | ||
- | * If the worksheet is to be created regularly by overwriting previous targets | + | * If the worksheet is to be created regularly by overwriting previous targets, for example a monthly snapshot worksheet without history, then ' |
- | * If the worksheet must be present and will be only appended | + | * If the worksheet must be present and will be only appended, for example a monthly snapshot added to an already existing history, then ' |
- | * If the worksheet must not be present | + | * If the worksheet must not be present |
* If the content of the worksheet will be overwritten no matter if it exists or not then ' | * If the content of the worksheet will be overwritten no matter if it exists or not then ' | ||
- | * Don' | + | * In case you do not know the MS Excel name of the worksheet, |
===== Referring to a worksheet ===== | ===== Referring to a worksheet ===== | ||
- | Once a worksheet is identified by **WSheet Use** it will be identified by its [[en: | + | Once a worksheet is identified by **[[en: |
- | Example: the report exported from an ERP system gets its Djeeni name as **ERPReport**. To refer to cell A3 on this worksheet write: | + | Example: the report |
< | < | ||
Line 37: | Line 37: | ||
Worksheets that are not in use from a certain point in the process can be released. Releasing a worksheet frees resources from Excel, and prevents accidental mistakes. It is advisable to explicitly release a worksheet if it is not in use anymore. Nevertheless, | Worksheets that are not in use from a certain point in the process can be released. Releasing a worksheet frees resources from Excel, and prevents accidental mistakes. It is advisable to explicitly release a worksheet if it is not in use anymore. Nevertheless, | ||
- | Worksheets can be released by the **WSheet Release** process step (in the Worksheet category). The only parameter is the [[en: | + | Worksheets can be released by the **[[en: |
===== List of Worksheets ===== | ===== List of Worksheets ===== | ||
- | SImple | + | Simple |
- | To specify a list of workbooks | + | To specify a list of workbooks, instead of **[[en: |
Look [[en: | Look [[en: | ||
Line 49: | Line 49: | ||
===== Example: Data consolidation ===== | ===== Example: Data consolidation ===== | ||
- | There are several | + | Several |
+ | * to company level; | ||
+ | * to yet another, | ||
+ | * on one worksheet; | ||
+ | * having another template. | ||
+ | The process looks like that: | ||
- | * First the target worksheet should be identified and used: **WSheet Use** | + | * First, the target |
- | * Then a workbook list should be set up to go through the workbooks in the report folder: **WBook List Start**. The Djeeni name of the workbook list will be used to identify the current report worksheet. | + | * Then a workbook list should be set up to go through the workbooks in the report folder: **[[en: |
- | * Select the necessary range from the current report worksheet and copy them to the appropriate location in the company template: **Range Copy/Move** (see [[en: | + | * Select the necessary range from the current report worksheet and copy them to the appropriate location in the company template: **[[en: |
- | * Go to the next report worksheet: **WBook List Next**. Djeeni releases the current and uses the next report worksheet. | + | * Go to the next report worksheet: **[[en: |
- | * Optional: release the consolidated company worksheet (Djeeni keeps target worksheets open to let the result | + | * Optional: release the consolidated company worksheet (Djeeni keeps target worksheets open to let you check the result) |