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.
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
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
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.
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.
If errors occurs whilst using other tabs, the settings tab will display a red dot indicating that there is a problem.
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.
Tags can be selected in the tree and then moved to the "Selected tags" field by drag and drop.
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).
Object Search
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:
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.
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:
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:
To use the Range option, select Range in the panel then click "…". The paths from the previous example can be selected in the worksheet.
Click OK, then select an empty cell in the worksheet and click Create.
To refresh the values in the cells you can click on Force Calculate button in the "INMATION" tab of the Excel sheet.
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.
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.
| 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.
To write a value to the selected object path(s), enter a value in the field and click Create.
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.
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).
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.
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.
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.
| 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.
| This format should also be used for the Start and End time arguments for the history read functions |