Excel

The Excel block can read and write Excel documents and supports documents with formulas and macros, allowing to integrate Excel calculations into pSeven workflows.

Introduction

The Excel block is intended to integrate Excel documents into pSeven workflows. 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 its 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 on Windows 8 (64-bit).
  • Excel 2013 (64-bit) running on Windows 7 (64-bit).
  • Excel 2016 (64-bit) running on Windows 7 SP1 (64 bit).
  • Excel 2019 (64-bit) running on Windows 10 Pro version 1803 (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).
  • Binary Excel workbook (.xlsb).

Configuration Dialog

The Excel block configuration dialog contains:

  • The Document file pane: used to select the working Excel document which will be used in calculations. This document 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. See Document for details.
  • The Export file pane: used to select a file which saves changes made by the block. It can be the same file as the working document selected on the Document file pane, or you can save changes to another file. If an export file is not specified, no changes will be saved by the block. For details, see Export.
  • The Variables pane: shows added input and output variables. Variables are used to map cells in the working document to block’s input and output ports. New variables can be added either by selecting cells in the loaded document file or manually on the Variables pane. For details, see Variables.
  • The Macro name pane: enables to check document macros from the block’s configuration dialog. Refer to Macros for details.

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, click b_xlsload on the document toolbar to load the file and show its contents. You can also open the document in Excel using the b_xlsopen button. After loading the document into the block, you can quickly add variables by selecting the cells to read or write via b_blconf_read or b_blconf_write buttons on the document toolbar or add the variables manually on the Variables pane (see section Variables for details).

../_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 for reading and writing — for example, they are 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 also shows a warning on the Issues pane because cell values are 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.

Note that the loaded document content can be cleared using the b_common_sweep button on the document toolbar.

While configuring a block with a 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.

The path to the Excel file can be sent directly to the Document file input port. This path can be absolute or relative. A relative path is resolved as a path inside the current project directory: for example, ./data/MyData.xls points to a file in the data/ subdirectory inside your project. The leading dot . represents the project directory; it is recommended to include the ./ part in relative paths. You can also select this port as a workflow parameter to change the document path from Run.

Note that when you select a file on the Document file pane, its path becomes the value assigned to the Document file port.

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 the file where to save. There are two options:

  • You can select the same file as on the Document file pane. This configuration is similar to using the Save command in Excel. Note that if you keep the document open in Excel, it may be necessary to reload it see values updated by the block.
  • 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 the file with changes, and the original document file will not be changed.

Likewise the document path, the path to the export file can be sent to the Export file port, and the same rules apply to relative paths. Also note that when you select a file on the Export file pane, its path becomes the value assigned to the Export file port.

Variables

Variables can be added to the block either by selecting cells (a single cell or a cell range) in the loaded document or manually (without loading the document) on 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 you specify the name that does not exist yet, the block automatically creates a variable with this name and maps it to a port of the corresponding type.

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

../_images/page_blocks_Excel_exc3.png

In the Add variables dialog you can add multiple variables at once by specifying a comma-separated list of names. This feature is intended as a fast way to add many variables which you then edit on the Variables pane. If you use a list of names, the initial properties of all added variables will be the same, because settings in the dialog apply to each variable in the list.

  • Name: specifies the name for a new variable or a comma-separated list of names to add multiple variables.
  • Sheet: selects the working sheet.
  • Cells: specifies a single cell or a cell range to read or write on the selected working sheet. For example, ("A1") is a single cell, ("A1:A5") is a continuous range which contains five adjacent cells. The block can also work with disjoint ranges which include non-adjacent cells — for example, ("A1:A3", "C1:C3"). For more details on using disjoint ranges, see Working with Disjoint Ranges.
  • Ports: selects the ports to create. Note that if you are adding multiple variables, this setting applies to all of them.
    • Input: the block will write values (numbers, strings or matrices) received to its input port to the specified document cells before running Excel calculations.
    • Output: the block will read values from the specified document cells after running Excel calculations and send these values to the output port.
    • Both: the block will write values to the specified document cells, run Excel calculations, then read new values and send them to the output port. This option creates a pair of input and output ports, and both ports will have the same name (an inout variable).
  • Type: specifies the type of a variable to read or write. Input and output variables can be RealScalar, RealMatrix, StringScalar or StringMatrix.
  • Transpose: if enabled, the block will apply transposition to the value received to the input port or sent to the output port. For input variables, the block writes a transposed value to the selected cell range. For output variables, the value from Excel document will be transposed after reading.

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 in the document.

Macros

If your Excel document contains macros, you can configure the block to run a macro after writing input variables to the document. The block detects macros when you load the document and shows them in the drop-down list below the document preview.

../_images/page_blocks_Excel_check_macro.png

The macro you select in this list will run each time this block starts in a workflow. You can also test the selected macro without running a workflow, using the b_xlsmacro button.

The block allows to select one macro only. If you need to run multiple macros, you can create an additional macro in Excel which calls other macros in the required order.

Note

Trust access to the VBA project object model should be enabled in your Excel settings in order to detect macros in the document automatically. To enable it, open the Properties dialog in Excel (File ‣ Properties), select the Trust Center tab and click the Trust Center Settings button. In the Trust Center Settings dialog, select the Macro Settings tab and check the “Trust access to the VBA project object model” option in the Developer Macro Settings group on this tab.

The macro to run can also be specified manually simply by copying its name from Excel to the Macro name input field or to the value of the Macro name option. To find names of macros in your document, bring up the Macro dialog in Excel ( Alt F8 ) which will show available macros.

If you know that some macro exists in the document, but its name does not appear in the Excel’s Macro dialog, this macro is probably a private function. The block can run private macros too, but you will have to manually find out its full name including the name of module, sheet or other object where this macro code is located:

  • In Excel, switch to the VBA editor ( Alt F11 ).

  • Select View ‣ Object Browser from the editor’s menu ( F2 ).

  • In Object Browser, use the search function to find where your macro is located.

  • In the Search Results box, you can select a name to quickly view its details in the bottom pane of the Object Browser — for example, it can show:

    Private Sub hiddenMacroName()
      Member of VBAProject.MyModule
    
  • Copy the module and macro names (without the project name) to obtain the name which should be used in the Excel block configuration, for example: MyModule.hiddenMacroName.

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

  • If the calculation mode is Manual (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 before recalculating formulas in the document.
    • The block requests document recalculation in Excel. Excel recalculates formulas and changes cell values in the document.
    • Output variables are read from the document, the block outputs their values, saves the export file (if configured so), and finishes.
  • If the calculation mode is Automatic, 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, the block outputs their values, saves the export file (if configured so), and finishes.
  • If the calculation mode is Automatic except tables, data tables in the document are not recalculated every time. In this mode, a data table is recalculated only when a change is made to it. Otherwise this mode works the same as Automatic.
  • If the calculation mode is No recalculation, the document is not recalculated by the block. Also, if this mode is selected, the macro does not run, so this setting effectively disables macros.

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

Advanced

This subsection provides more details on configuring the Excel block for reading and writing values in situations when its behavior is rather complicated. See section Variables for general information on adding and configuring variables.

Writing Matrix Values of Unknown Shape

There are cases when the dimension of input data received by the Excel block is unknown at the moment when you configure it for writing values to the document. For example, Excel can be working in a cycle where it receives input from a Design space exploration block working in batch mode. Every data batch sent by the Design space exploration block is a matrix where each row is another data point. The number of points in a batch can vary, so the matrices coming to Excel can have different number of rows.

The matter is that generally when writing a matrix value to the document, the Excel block tries to fit that value to the variable’s cell range. For instance, if the adjacent range ("B2:B3") is selected as the variable value, a 2×1 input matrix will perfectly fit into this range. If the dimension of the incoming matrix is unknown, selecting the appropriate cell range for writing values to the document becomes a challenge.

To solve the problem, follow two simple steps:

  • Use the Write into the greater range option. This option is enabled (True) by default which means that the Excel block allows writing values to the selection larger than the data received to the port. Note that if the option is disabled (False), the block requires an exact match of the incoming value and the cell range shapes, and raises an error if the input data shape is different from the selection.
  • Select a cell range in the loaded document (to be mapped to an input variable) that will easily hold all data values received to the port.

When you run the block, its behavior will be as follows: after receiving values of input variables, it fills the selected range with data starting from the leftmost corner and then clears all “empty” cells (the ones beyond the incoming matrix dimension). In other words, if you mapped a selection ("A11:C19") to the input variable x and the shape of the incoming matrix is 2×2, the contents of all cells except for the range ("A11:B12") will be cleared.

Warning

The Excel block does not support writing matrix values to a range containing merged, hidden or any other specific type of cells, as these values may not properly fit into the shape.

Working with Disjoint Ranges

The Excel block allows mapping a set of nonadjacent cells or ranges to one input or output variable. It means that the block can actually read values of variables from a disjoint selection and write data from a single variable to such a selection. The block’s behavior for standard read and write operations is outlined in use-cases below.

Reading Values

Consider the following configuration where the output variable x is mapped to a disjoint range (highlighted yellow):

../_images/page_blocks_Excel_advanced_1.png

Before sending values to the output port, the block goes through the following steps:

  • it tries to create a rectangular shaped range that would contain all selected cells mapped to the output variable x (in our example, a 6×3 matrix);
  • cells not included into the selection will be considered virtually empty;
  • if the rectangular shape it tries to create contains some cells not included into your selection, the values from these cells are ignored, and corresponding elements of the output matrix are filled with None values (or left empty in case of a StringScalar matrix).

In our example, the block produces the following matrix:

../_images/page_blocks_Excel_advanced_2.png

Note that for our configuration of the output variable you can use the Delete blank rows and columns option (disabled by default). Setting the option’s value to True makes the block remove all rows and columns that contain empty cells (None values are interpreted by Excel as empty cells) from the output matrix. The resulting matrix is shown below.

../_images/page_blocks_Excel_advanced_3.png

As you may notice, this option changes the shape of a matrix. It becomes important if there are blocks in the workflow which receive values from the Excel block.

Writing Values

Consider the block’s configuration from the previous example. Now the input variable f is mapped to a disjoint range (highlighted in blue).

../_images/page_blocks_Excel_advanced_4.png

In case of writing to a disjoint selection, it is necessary to correctly specify the dimension of an input matrix. To solve the problem, follow these simple steps:

  • Identify a matrix of a minimum rectangular shape including your selected ranges (in our example, a 6×3 matrix).
  • Virtually remove all rows and columns that are either physically empty or contain unwanted data (in our example, cell ranges ("B8:B13") and ("A10:C11")).
  • The resulting matrix shape (4×2) is used as the value sent to the input port.

Specify the value for the input variable taking into account the required matrix dimension. Let it be ((2003.0, 4000.0), (2004.0, 1925.0), (2005.0, 5200.0), (2006.0, 7100.0)).

When writing a matrix value to the document, the block fills the selected range with data starting from the leftmost corner.

../_images/page_blocks_Excel_advanced_5.png

Note that to see updated values in the selected document cells, you will have to configure an export file — see section Export for details.

Note

The Write into the greater range option, which is enabled by default, is ignored for disjoint ranges. That is, the matrix you write to a disjoint range must be of the required shape even if Write into the greater range is enabled.

Options


Calculation mode

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

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

Selects 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 — Excel will recalculate the document only when requested by pSeven.
  • Automatic — Excel controls recalculation and automatically recalculates the document on every change.
  • Automatic except tables — Excel automatically recalculates all parts of the document except data tables. A table is recalculated only when a change is made to it.
  • No recalculation — The block will not recalculate formulas, cell and data table values in the document in case of any changes. Also all macros will not run.

Note that Manual is recommended for most cases, as 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.

Document read and write options.Close document at each step

If enabled, the document is closed at the end of each calculation step.

Value:True (enabled) or False (disabled)
Default:True (enabled)

New in version 6.12.

If the option is True, the document is closed at the end of each calculation step. Note that if this option is False, other blocks and applications will be unable to write to the loaded document.

Document read and write options.Delete blank rows and columns

If enabled, blank rows and columns in an output matrix will be removed.

Value:True (enabled) or False (disabled)
Default:False (disabled)

New in version 6.12.

The option is available only for output variables (the values read by the block).

If set to False (default), the block will output a matrix fitting into the selected range, if some cells in the range are empty, they will be filled with None values. If the option’s value is True, the block will remove all rows and columns that contain empty cells from the output matrix. If the data is not rectangular, the result may contain None values to fill empty cells. See section Reading Values for details on using the option.

Document read and write options.Read evaluation errors as NaN values

If enabled, the block interprets evaluation errors on reading as NaNs.

Value:True (enabled) or False (disabled)
Default:True (enabled)

New in version 6.12.

When the option is set to True (default), Excel evaluation errors like #NUM!, #DIV/0!, and others are read as NaN values. Otherwise, the block raises an error when it encounters such cells in the document.

Document read and write options.Write into the greater range

If enabled, the Excel block allows writing a matrix value to the selection larger than the data received to the port.

Value:True (enabled) or False (disabled)
Default:True (enabled)

New in version 6.12.

If set to False, the block requires an exact match of the incoming value and the cell range shapes. In case a matrix fails to fit into the selection, the block raises an error. See sections Writing Matrix Values of Unknown Shape, Writing Values for details on using the option. Note that this option is ignored for disjoint ranges (always behaves as False). However, it continues to work for ordinary ranges even if there are any disjoint ranges in Excel configuration.

Error handling behavior

The action to perform if the block encounters an error.

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 executable path

Path to the Excel executable (EXCEL.EXE).

Value:string (Excel executable path)
Default:empty string (find Excel automatically)

Sets the path to the Excel executable. This option is not required: when empty (default), the block tries 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 wait timeout

The time in seconds to wait for the Excel application to become available in case it is currently used by another Excel block.

Value:positive integer
Default:300

Several Excel blocks in a workflow can start simultaneously — this is typically the case when there is a Composite block (with parallel execution enabled) containing Excel blocks. In this case, only one of the Excel blocks can connect to the Excel application at a time (this is an Excel limitation). Other blocks have to wait until Excel becomes available, and will do so until the timeout is exceeded.

Exceeding the timeout raises an error which always stops the workflow. That is, the timeout error is never suppressed regardless of the Error handling behavior setting. The workflow is not terminated immediately: all blocks that are currently running continue their execution, and the workflow stops after they finish (soft stop). Note that setting the timeout to 0 will always trigger workflow stop, if an Excel block with zero timeout starts while there is another Excel block running. The running block will be allowed to finish execution in this case, and the workflow will be stopped by the timeout error from the second block.

Macro name

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

Value:string (macro name)
Default:"No macro"

The specified document macro will run after the block writes values of input variables to the document. This option is set automatically when you specify a macro on the Configuration tab.

Note that the behavior of macros run by the block depends on the selected document recalculation mode (the Calculation mode option):

  • If set to Manual, the macro takes priority over cell formulas in the document.
  • If set to Automatic or Automatic except tables, the document is recalculated before running the macro.
  • If set to No recalculation, the macro does not run.

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

New in version 6.11.

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 you configure the block to work with an Excel document which is changed by an external application while your workflow is running, it is recommended to set this option to 1. This setting ensures that the block keeps Excel open only while it works with the document, so it does not block changes to the document made by the external application.

Visible

Show the Excel application window during run-time calculations.

Value:True (show) or False (do not show)
Default:True (show the Excel window)

When your workflow runs, the block launches Excel in a visible window by default. If you set this option to False, Excel is launched in the background without showing a window.

This option affects the workflow run-time only. When you configure the block — for example, load an Excel document to show its contents in the configuration dialog — Excel is always launched in the background and its window is not visible.

Known Issues

  • If the Excel block fails for some reason, removing the contents of the C:\Users\%USERNAME%\AppData\Local\pSeven may resolve the issue.
  • Sometimes the import of macro names fails because of Excel error. In this case, input the macro name manually.