Using the inExcel Add-in

Once the Add-in has been activated the "INMATION" tab will be visible in the Excel menu bar when Excel is opened.

Excel Menu Bar with tab
Figure 1. Excel Menu Bar with tab

The inExcel Add-in has two main functionalities available:

Action Pane

This graphical helper for the inExcel Add-in allows you to browse the different models in the system, select tags, perform read/write actions and configure connection settings to the Core.

VBA Functions

The inExcel Add-in will add inmation functions to the current worksheets and retrieve data into Excel cells.

The Force Calculate button is used to recalculate all functions on the worksheet.

Action Pane

With an Excel workbook open, select the "INMATION" tab and click on Show Actions Pane (this button toggles between show/hide). The Action Pane is displayed and contains four tabs:

  • System Explorer

  • Object Search

  • Functions

  • Settings

Actions Pane - Tabs
Figure 2. Actions Pane - Tabs

Settings

The Settings tab contains the Core connection settings and other information regarding time, language and OPC settings. The Settings tab also contains the Session Log that reports any errors that occur with the Excel Add-in.

Web API Connection

The inExcel Add-in uses the Web API to communicate with the Core. To successfully connect to the Core the following fields need to be entered:

  • Web API address - The hostname or IP address of the Web API server (for example "http://localhost")

  • Port - The communication port for the Web API server (default is 8002)

The WebAPI supports both Windows authentication and Profile Credentials. Select which authentication method you wish to use from the dropdown menu.

If using Profile Credentials, you will also need to enter:

  • Profile - Profile name (must match a Profile object in the Access Model) to connect to Core

  • Password - Password for the profile used to connect to the Core

Settings tab - Actions Pane
Figure 3. Settings tab - Web API connection

Click Initialize to connect the inExcel Add-in to the Web API.

SCI Connection

In system:inmation versions 1.60 and older, the inExcel Add-in uses the SCI to connect to the Core. Only inmation authentication is available in these older versions. The connection parameters for these versions are as shown below:

  • Hostname or IP - The hostname or IP address of the Core server

  • Port - The communication port for the Core Server

  • Profile - Profile name to connect to inmation Core

  • Password - Password for the profile used to connect to the Core

If no current connection exists, click Initialize to form the connection to the Core.

Settings tab - Actions Pane
Figure 4. Settings tab - SCI Core connection

Connection Information

The Current connection section displays information about the existing connection and if the UDFs (User Defined Functions) are OK (UDFs are covered in the the description of the "Functions" tab.

Session Log and Write to log file options

The Session Log displays any encountered errors with the Excel Add-in.

Session Log Errors
Figure 5. Session Log Errors

If errors occurs whilst using other tabs, the settings tab will display a red dot indicating that there is a problem.

Red dot indicating Session log errors
Figure 6. Red dot indicating Session log errors

A log file for all session log entries can also be created by checking the Write Log File checkbox.

The file will be created in the user directory in the following location:

"C:\Users\<current user>\AppData\Local\inmation\ExcelAddin\Log"

System Explorer tab

When connected to the Core, the System Explorer will display the Model Panels available in DataStudio. You can select the model panel to display from the drop down menu (I/O, KPI, Server etc.). The tree in the system display can be expanded and navigated as you would in DataStudio.

I/O Model in System Explorer
Figure 7. I/O Model in System Explorer

Tags can be selected in the tree and then moved to the "Selected tags" field by drag and drop.

Drag and drop tags to Selected Tags
Figure 8. Drag and drop tags to Selected Tags

Double-clicking on the items in the tree will also add them to the Selected Tags section. All items added are then available to perform Read/Write functions on in the "Functions" tab (this will be covered in a following section).

The Object Search tab allows you to search for objects in the namespace in much the same way as the Object Search Panel in DataStudio. Enter a search term in the field to retrieve items with matching object names:

Object Search tab
Figure 9. Object Search tab

Items retrieved in the search can be directly added to the "Selected tags" by drag and drop. The search options can be changed to also search the object path name or to only search for dynamic objects. To do this select the drop down field to the right of the search field and check the appropriate check boxes.

Object Search Options
Figure 10. Object Search Options

Functions Tab

In the Functions tab, you can create functions in the Excel sheet to Read/Write to items in the connected Core namespace. You can execute real-time and history reads on items and the value will be retrieved and returned in the selected worksheet cells. Expand the ReadValue part of the tab to see the options:

Functions - ReadValue
Figure 11. Functions - ReadValue

ReadValue

The items to read can be configured in the following ways:

  • Manual - Enter the path of the item to be read manually

  • Selected - All the items added to the Selected tags field (see previous section) will be read

  • Range - Paths of items to be read can be selected from a range of cells in the worksheet

It can also be selected to return the quality and timestamp from the items by checking the appropriate check boxes. For this example we will use the "Selected tags" added in the System Explorer tab and check the quality and timestamp boxes. Click Create to retrieve the values and return the selections in the worksheet:

ReadValue Example
Figure 12. ReadValue Example

To use the Range option, select Range in the panel then click "…". The paths from the previous example can be selected in the worksheet.

ReadValue - Range option
Figure 13. ReadValue - Range option

Click OK, then select an empty cell in the worksheet and click Create.

ReadValue - Range option Values
Figure 14. ReadValue - Range option Values

To refresh the values in the cells you can click on Force Calculate button in the "INMATION" tab of the Excel sheet.

Force Calculate button
Figure 15. Force Calculate button

ReadHistoricalData

The ReadHistoricalData functions work in a similar manner to ReadValue with the same method for selecting paths. If the Selected paths is chosen the items selected in the Explorer tab.

ReadHistoricalData function

The Start and End time for the data can be selected, as well as the aggregate and number of retrieved intervals for the history call.

Clicking Create will place the historical data in the worksheet with the object paths for the selected items heading columns of retrieved data.

Retrieved Historical Data in Worksheet
If the RAW aggregate is used for history calls then the maximum number of values retrieved will be equal to the entered Intervals number. This is different to the way Raw history calls are handled in the rest of system:inmation and is specific to the inExcel Add-In. To retrieve the full raw historical data for the selected time period, a high interval number (equal or greater than the estimated number of raw values) should be entered.

WriteValue

The WriteValue functions work in a similar manner to ReadValue with the same method for selecting paths.

WriteValue function

To write a value to the selected object path(s), enter a value in the field and click Create.

WriteValue Results

The worksheet displays the paths of the selected objects and the result of the write operation.

Users can also specify the datatype for the WriteValue operation by selecting from the dropdown menu. The written value will then be saved as the selected datatype.

WriteValue choose datatype

If you want to write an array value then the Array checkbox should be selected and the array value entered using the vertical bar character as a delimiter (for example 1|6|99 or a|b|c|z).

WriteValue Array

Functions

The Functions button in the "INMATION" tab of Excel allows you to call the Read and Write functions without using the Action Pane. Select a cell in the worksheet and click on Functions to show the drop-down menu.

Functions menu
Figure 16. Functions menu

Selecting ReadValue from the menu will open up a dialog where the arguments for the functions can be added. The Paths arguments can be added by selecting a range of cells from the worksheet or by entering a valid path manually. The ShowValue, ShowTimestamp and ShowQuality arguments are all boolean so TRUE or FALSE should be added.

Function Arguments
Figure 17. Function Arguments

Click OK to enter the formula and the retrieved values will be returned. The function can also be edited manually in the cell with a valid path name and arguments.

inExcel functions - Editing cell value
Figure 18. inExcel functions - Editing cell value
For the function to return a valid value, ALL arguments must be filled in the cell. For example the ReadValue function, all the ShowValue, ShowTimestamp and ShowQuality arguments must have a value for the function to work.

When retrieving timestamps using an inExcel function, the cell format must be changed to display the timestamp properly. For example, using the custom format shown below.

Timestamp cell format
Figure 19. Timestamp cell format
This format should also be used for the Start and End time arguments for the history read functions