User Tools

Site Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
en:user_guide:worksheet_basic [2021/01/10 13:54]
tiger [Example: Data consolidation]
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 (numberstextsdates) is in cells+  * Data i.e.numbertext, 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 be grouped into one workbook or they can be found in different workbooks.+  * 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 logical name ([[:en:concepts:djeeniname|Djeeni name]]to the worksheet making your process more readable and understandable.+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 [[:en:concepts:djeeniname|Djeeni name]] to the worksheet making your process more readable and understandable.
  
 ===== Using worksheets ===== ===== Using worksheets =====
  
-To identify a worksheet for the Djeeni process the process step **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** (to be found on the [[en:user_interface:toolbar|Process Toolbar]] under the Worksheet category) should be used. Once it is added to the process you can specify its [[en:process_steps:worksheet:wsheet_use|parameters]]. Tips to fill out the parameters+To identify a worksheet for the Djeeni process the process step **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** should be used which is found on the [[en:user_interface:toolbar|Process Toolbar]] under the Worksheet category. Once it is added to the process you can specify its [[en:process_steps:worksheet:wsheet_use|parameters]]. Tips to fill out the parameters
  
-  * 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 in the popup window to select the workbook. +  * 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 on the popup window to select the workbook. 
-  * Choose a [[:en:concepts:djeeniname|Djeeni name]] that describes best the data in the worksheet. This name will be used throughout the process to access any data on this worksheet. +  * Choose a [[:en:concepts:djeeniname|Djeeni name]] that describes best the data in the worksheet. This name will be used throughout the process to access data on this worksheet. 
-  * Choosing 'source' as the worksheet type means that Djeeni protects the file from being accidentally modified/overwritten. This also ensures that in case of a mistake in the process you can modify and re-run it without problem as all the sources are kept as they were.+  * Choosing 'source' as the worksheet type means that Djeeni protects the file from being accidentally modified/overwritten. This also ensures that in case of a mistake in the process you can modify and re-run it without problem as all the sources are kept as they were.
   * Use the 'If Target then worksheet action' as follows:   * Use the 'If Target then worksheet action' as follows:
-    * If the worksheet is to be created regularly by overwriting previous targets (for example a monthly snapshot worksheet without historythen 'Exists:Delete and Recreate / Not Exists:Create' +    * If the worksheet is to be created regularly by overwriting previous targetsfor example a monthly snapshot worksheet without historythen 'Exists:Delete and Recreate / Not Exists:Create' 
-    * If the worksheet must be present and will be only appended (for example a monthly snapshot added to an already existing historythen 'Exists:Use as is / Not Exists:Error' +    * If the worksheet must be present and will be only appendedfor example a monthly snapshot added to an already existing historythen 'Exists:Use as is / Not Exists:Error' 
-    * If the worksheet must not be present (previous version must be moved before creating the new one) then 'Exists:Error / Not Exists:Create'+    * If the worksheet must not be present i.e., the previous version must be moved away before creating the new version, then 'Exists:Error / Not Exists:Create'
     * If the content of the worksheet will be overwritten no matter if it exists or not then 'Exists:Use as is / Not Exists:Create'     * If the content of the worksheet will be overwritten no matter if it exists or not then 'Exists:Use as is / Not Exists:Create'
-  * Don'you know the MS Excel name of the worksheet? No problem, define its position. The first worksheet is 1, the second is 2 and so on. It is a very handy option if you get multiple workbooks from multiple countries containing the same (mostly only) worksheet with different names.+  * In case you do not know the MS Excel name of the worksheet, you can define its position. The first worksheet is 1, the second is 2 and so on. It is a very handy option if you get multiple workbooks from multiple countries containing the same worksheet with different names.
  
 ===== Referring to a worksheet ===== ===== Referring to a worksheet =====
Line 28: Line 28:
 Once a worksheet is identified by **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** it will be identified by its [[en:concepts:djeeniname|Djeeni name]] further in the Djeeni process. Once a worksheet is identified by **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** it will be identified by its [[en:concepts:djeeniname|Djeeni name]] further in the Djeeni process.
  
-Example: the report worksheet exported from an ERP system gets its Djeeni name as **ERPReport**. To refer to cell A3 on this worksheet write:+Example: the report worksheet exported from an ERP system gets the Djeeni name **ERPReport**. To refer to cell A3 on this worksheet write:
  
 <code> <code>
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, Djeeni also actively [[en:process_steps:worksheet|manages the worksheets]]. 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, Djeeni also actively [[en:process_steps:worksheet|manages the worksheets]].
  
-Worksheets can be released by the **[[en:process_steps:worksheet:wsheet_release|WSheet Release]]** process step (in the Worksheet category). The only parameter is the [[en:concepts:djeeniname|Djeeni name]] of the worksheet to be released.+Worksheets can be released by the **[[en:process_steps:worksheet:wsheet_release|WSheet Release]]** process step,in the Worksheet category on the Process Toolbar. The only parameter is the [[en:concepts:djeeniname|Djeeni name]] of the worksheet to be released.
  
 ===== List of Worksheets ===== ===== List of Worksheets =====
  
-SImple processes use some well-identified source and target worklists. They can be all specified with **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]**. Other processes have an unknown or variable number of worksheets. Typical examples are consolidation and splitting of worksheets where data processing should be done for each source or target worksheet. Djeeni automates such processes using //workbook lists//+Simple processes use some well-identified source and target worklists. They can all be specified with **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]**. Other processes have an unknown or variable number of worksheets. Typical examples are consolidation and splitting of worksheets where data processing should be done for each source or target worksheet. Djeeni automates such processes using //workbook lists//
  
-To specify a list of workbooks you should use instead of **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** the process step **[[en:process_steps:wbooklist|WBook List Start]]** (under the If/Lists category). You give this list a logical Djeeni name just like you give it to a worksheet specified by **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]**. At other process steps, you can use the Djeeni name of the workbook list to refer to the //current worksheet// in the list.+To specify a list of workbooksinstead of **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]**  you should use the process step **[[en:process_steps:wbooklist|WBook List Start]]** under the If/Lists category on the Process Toolbar. You give this list a [[en:concepts:djeeniname|Djeeni name]] just like you give one to a worksheet specified by **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]**. At other process steps, you can use the Djeeni name of the workbook list to refer to the //current worksheet// in the list.
  
 Look [[en:user_guide:worksheet_lists|here]] for a detailed explanation of workbook lists. Look [[en:user_guide:worksheet_lists|here]] for a detailed explanation of workbook lists.
Line 49: Line 49:
 ===== Example: Data consolidation ===== ===== Example: Data consolidation =====
  
-There are several departments delivering monthly financial information using a well-prepared template. Each department delivers a separate workbook containing the only report worksheet. The woorkbooks are collected in a specific folder. The process to be automated is to consolidate these reportsto company level to yet another workbook, on one worksheethaving another template.+Several departments are delivering monthly financial information using a well-prepared template. Each department delivers a separate workbook containing only one report worksheet. The workbooks are collected in a specific folder. The process to automate is consolidating these reports
 +  * to company level
 +  * to yet another, already existing, workbook; 
 +  * on one worksheet
 +  * having another template.
  
 +The process looks like that:
  
-  * First the target worksheet should be identified and used: **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** +  * Firstthe target company level worksheet should be identified and used: **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** 
-  * Then a workbook list should be set up to go through the workbooks in the report folder: **[[en:process_steps:wbooklist|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:process_steps:wbooklist|WBook List Start]]**. The [[en:concepts:djeeniname|Djeeni name]] of the workbook list will be used to identify the current report worksheet. 
-  * 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:user_guide:cellrange_basic#Copy or Move a range]]) +  * Select the necessary range from the current report worksheet and copy them to the appropriate location in the company template: **[[en:process_steps:range_cell:rangecopymove|Range Copy/Move]]** (see [[en:user_guide:cellrange_basic#Copy or Move a range]]) 
-  * Go to the next report worksheet: **[[en:process_steps:wbooklist|WBook List Next]]**. Djeeni releases the current and uses the next report worksheet+  * Go to the next report worksheet: **[[en:process_steps:wbooklist|WBook List Next]]**. Djeeni releases the current report worksheet and uses the next one
-  * Optional: release the consolidated company worksheet (Djeeni keeps target worksheets open to let the result be checked)+  * Optional: release the consolidated company worksheet (Djeeni keeps target worksheets open to let you check the result)