Excel

Tag: Integration

This block can read and write Excel documents and supports documents with formulas and macros, allowing to include Excel calculations in the workflow.

Introduction

The Excel block is intended to integrate Excel documents into a workflow. It maps specified cells in the document to input and output ports, allowing to write and read cell values. It also supports document recalculation and macros, enabling to control Excel calculations from the workflow.

When this block runs, it opens the selected document file in Excel and writes values received to input ports to the specified document cells. After this the block waits until Excel recalculates the document, then reads values from specified document cells and sends these values to output ports. Optionally the block can also run an Excel macro before or after recalculating the document — see section Macros for details.

By default, the document file on disk is not changed — the block edits the open file in Excel but does not automatically save it. If you want to save these changes, you will have to configure an export file — see section Export for details.

Note that when the Excel block executes, it actually launches the Excel application. It means that a workflow containing the Excel block can run only under Windows and on a host where Excel is installed. When you configure the block in Edit, Excel is launched only when you load the document in the configuration dialog — this is optional, so it is possible to configure the block without Excel installed.

Compatibility

The Excel block was tested for compatibility with:

  • Excel 2010 (32-bit) running in Windows 8 (64-bit).
  • Excel 2013 (64-bit) running in Windows 7 (64-bit).

Other versions of Excel are also compatible though they were not tested actively.

Supported file formats are:

  • Excel workbook (.xlsx),
  • Excel 2003 workbook (.xls),
  • Excel workbook with macros (.xlsm), and
  • binary Excel workbook (.xlsb).

Configuration

The Excel block configuration includes:

  • Document file: the Excel workbook that will be used in calculations. This file is also used when configuring the block with quick selections: when it is loaded into the block, you can quickly select cells and variables to read or write.
  • Variables: the block can define new variables — named values (numbers, strings, or matrices) that are mapped to cells in the document and to block’s input and output ports. Variables can be added either by selecting cells in the loaded document file or manually on the Variables pane.
  • Export file: the file to save changes made by the block. It can be the same file as the document, or you can save to another file. If an export file is not specified, no changes will be saved by the block.

Document

The Excel block works with documents saved in .xls, .xlsx, .xlsm, and .xlsb formats.

To select a document, click b_browse on the Document file pane and navigate to the file’s location. With the document selected, you can click b_xlsload on the document toolbar to load the file and show its contents. After loading the document into the block, you can quickly add variables by selecting the cells to read or write (see section Variables).

../_images/page_blocks_Excel_exc1.png

Document cells that are mapped to block variables are marked with different colors:

  • Blue marks the cells where the block writes (input variables).
  • Yellow marks the cells that are read by the block (output variables).
  • Green marks the cells that are both read and write — for example, mapped to a variable that has both input and output ports.
  • Red marks the cells that are mapped to two or more input variables (possible conflict). If such cells are found, the block will also show a warning in the Issues pane because cell value is undefined.

Note that Excel is required to load the selected file. However after you load the document, the block remembers its structure, meaning that:

  • A block with a loaded document can be configured on a host where Excel is not installed.
  • If you change the document in Excel, you will have to save it and then re-load it into the block.

While configuring a block with loaded document is more convenient, it is actually not required. If Excel is not available, you can skip loading the document in order to manually add variables and specify corresponding cells (see section Variables for details). Thus the block can be configured without Excel even if the document was not loaded earlier.

Export

When the workflow runs, changes in the document are not saved automatically — the block opens the document, changes and reads cell values but does not save the document file. If you want to save changes, click b_browse in the Export file pane and select where to save. There are two options:

  • You can select the same document file as the export file. Changes will be saved in the document, similarly to using the Save command in Excel.
  • You can select a different export file. This is similar to using the Save as... command in Excel: the block will save a copy of file with changes, and the original document file will not be changed.

Variables

Variables can be added to the block either by selecting cells in the loaded document or manually (without loading the document) from the Variables pane.

To add a variable from a selection, first mark the cells to read or write (hold Shift to select a range). After this click b_blconf_read (read from selection) or b_blconf_write (write to selection) on the document toolbar.

../_images/page_blocks_Excel_exc2.png

In the dialog you can select an existing variable (the one you have already added on the Variables pane) or input a new name. If the name you input does not exist yet, the block will automatically create this variable and map it to a port of corresponding type.

To add a variable manually, click b_blconf_add on the Variables pane. This command opens the Add variables dialog.

../_images/page_blocks_Excel_exc3.png

In the Add variables dialog you can add multiple variables at once, separating their names with a comma (all variables will get the same type and cell range, but these properties can be easily changed on the Variables pane).

  • Name: input the name for a new variable or a comma-separated list of names to add multiple variables.
  • Cells: specify document cells to read or write. Note that the sheet name should be included, for example Sheet1!A1 for one cell or Sheet1!B2:B5 for a cell range. If you are adding multiple variables, cells will be the same for all of them and you will need to edit cells on the Variables pane.
  • Ports: select the ports to create. If you are adding multiple variables, this setting applies to all of them.
    • Input: the block will write the value received to the input port into the document before running Excel calculations.
    • Output: the block will read the value from a document after running Excel calculations and send this value to the output port.
    • Both: the block will write the received value to the document, run Excel calculations, then read the new value and output it. This option creates a pair of input and output ports, and both ports will have the same name (an inout variable).

Using the Variables pane you can also quickly find the document cells mapped to any variable: select a variable, then click b_locate on the toolbar to select corresponding cells on the document pane.

Macros

If your Excel document contains macros, you can configure the block to run a macro after writing input variables to the document. The macro to run is specified using the Macro name option (see the Options tab in the configuration dialog). Its value can contain only a single macro name, running multiple macros is not supported (you can create an additional macro to handle this in Excel).

To find names of macros, you can use the Macro dialog (Alt F8) in Excel. If some macro is not found there, it is probably a private function. The block can run private macros too, but you will have to find out its name manually. First switch to the VBA editor: select any macro and click “Edit” in the Macro dialog. Then locate the macro code — it may be contained in some code module, workbook code or sheet code. In the Excel block configuration, use the full name of the function including the name of the object or module where it is contained — for example, Module1.myMacro or Worksheet____1.myMacro.

Note that the macro behavior depends on the Calculation mode option:

  • If manual calculation mode is selected (default), document recalculation is controlled by the block and goes in the following order:
    • The block writes input variables to the document.
    • The block runs the specified macro (without recalculating formulas in the document).
    • The block requests document recalculation in Excel.
    • Output variables are read from the document and the block finishes (also saves the export file if configured).
  • If automatic calculation mode is selected, the document is automatically recalculated by Excel on every change, thus changing the order to the following:
    • The block writes input variables to the document.
    • Excel recalculates formulas and changes cell values in the document.
    • The block runs the specified macro (in the recalculated document).
    • Output variables are read from the document and the block finishes (also saves the export file if configured).

Thus in the manual calculation mode (default) the macro takes priority over cell formulas in the document, while in automatic mode the macro runs when all cells are already recalculated.

Options

Calculation mode

The calculation mode to select when processing the document file in Excel.

Value:Manual, Automatic, or Automatic except tables
Default:Manual

Select how Excel will recalculate formulas and cell values in the document when it is changed by the block. This option is similar to the Excel calculation mode settings found in Excel options.

  • Manual — recalculation is controlled by pSeven. The block issues the manual recalculation command after making document changes.
  • Automatic — recalculation is controlled by Excel. Entire document is automatically recalculated on every change.
  • Automatic except tables — same as Automatic but data tables in the document are not recalculated every time. In this mode a table is recalculated only when a change is made to it.

Manual recalculation mode is recommended in most cases because the block will request recalculation only after it finishes writing to the document, thus avoiding extra calculations. In automatic modes, while the block writes to the document, Excel will run calculations each time the block writes a new value.

Error handling behavior

Common option that controls the block’s behavior when it encounters an error during execution. See section Error Handling for details.

Value:stop workflow, output defaults and signal, or output signal only
Default:stop workflow

When set to stop workflow, the block simply reports an error and the workflow interrupts.

If set to output defaults and signal, the block suppresses the error. In this case output ports send values assigned to them (the defaults), and the done port outputs False (the failure signal). Note that the block automatically assigns values to output ports that are created when you add variables. These values are:

The output signal only behavior means to output only the False value to done; nothing is output to other ports.

Excel document file

Full path to the Excel document file.

Value:path string
Default:empty

Stores the path to the selected document file. Can also be used to set this path instead of selecting a document file on the Configuration tab.

Excel executable path

Full path to the Excel executable (EXCEL.EXE).

Value:path string
Default:empty

Sets the path to the Excel executable. This option is not required: when empty (default), the block will try to determine the path automatically. The option can be used to select a specific version of Excel or to configure the block if it could not find the Excel installation.

Excel export file

Full path to the Excel export file.

Value:path string
Default:empty

Stores the path to the selected export file. Can also be used to set this path instead of selecting an export file on the Configuration tab.

Macro name

The macro to run in Excel after the block writes input values to the document.

Value:string
Default:empty string

Specified document macro will run after the block writes values of input variables. Note that results depend on the selected Calculation mode: if manual calculation mode is selected, the macro takes priority over cell formulas in the document, while if automatic calculation mode is selected, the document is recalculated before running the macro (see section Macros for details).

Max steps

The maximum number of times that the block can use the same Excel application instance.

Value:positive integer
Default:50

Normally, the Excel application started by the block is not closed after the block finishes (that is, until the workflow stops). Subsequent startups of the block use the same application instance to save time. This option sets the number of block startups that are allowed to use the same running Excel instance. When the limit is reached, Excel is restarted.

This option works only when Excel is started by the block. If the block connects to an instance of Excel that you have started manually, this instance is never restarted.

If Excel document is changed by an external application during the workflow run, we recommend you to set this option to \(1\). In this case, the block keeps Excel opened only during the block startups and does not prevent other applications from changing the document.

Visible

Whether to show the Excel application window during run-time calculations.

Value:Boolean
Default:True (show the window)

If False, Excel will run in the background without showing a window. This option affects only the run-time. When you configure the block (load the document’s contents to show them in the configuration dialog), Excel is always launched in the background without showing a window.