How to Link Microsoft Excel to Fernhill SCADA

Introduction

You can link a Microsoft Excel spreadsheet to Fernhill SCADA using the ODBC driver provided with Fernhill SCADA. You can configure Microsoft Excel to control how the data is refreshed:

Data can be refreshed when the spreadsheet is opened, or
Data can be refreshed when you click Refresh All or Refresh, or
Data can be refreshed at a configured interval.

Prerequisites

Step 5 of this guide requires an ODBC Data Source Name to be configured for Fernhill SCADA. For more information see Setting up an ODBC DSN.

Linking Microsoft Excel to Fernhill SCADA

1. Start Microsoft Excel

2. On the Data tab, in the Get External Data group, click From Other Sources. The drop-down menu opens:

3. Select From Data Connection Wizard. The Data Connection Wizard dialog opens:

4. In the What kind of data source list box, select ODBC DSN. Click Next>. The Connect to ODBC Data Source page opens:

Note: The ODBC Data Source Name (DSN) you created as a prerequisite to this walkthrough might not be visible. For example, if you are running the 64-bit version of Microsoft Excel, a 32-bit System DSN would not appear in the list. If necessary, create a DSN that is compatible with the version of Microsoft Excel you are using.

5. In the ODBC Data Sources list box select My Fernhill SCADA. If you used a different name when setting up your ODBC User Data Source Name, use that name instead. Click Next>. The Select Database and Table page opens:

6. Scroll down and select the table TagDataPointAnalog. Click Next>. The Save Data Connection File and Finish page opens:

Note: Information about the data connection will be saved to the file TagDB TagDataPointAnalog.odc. If you link the same table multiple times, you will need to choose a different name for each data connection file.

7. Click Finish. The Data Connection Wizard will close and the Import Data dialog opens:

Note: You can click Properties to open the Connection Properties dialog. From this dialog you can edit the query and control how often it is refreshed.

8. Click OK to import the data table. Your spreadsheet should now look similar to:

Excel spreadsheet showing TagDataPointAnalog table

Using VLOOKUP to Extract Data

You can use the spreadsheet function VLOOKUP to extract values from the table. For example:

= VLOOKUP( "Level", Table_TagDB_TagDataPointAnalog, 31 )

This function extracts column AE (DisplayValue) of the "Level" tag row.

Importing Only the Data You Need

Importing an entire database table may not be practical. You can reduce the size of the imported database table by removing columns from the query, or adding a WHERE clause to the query. To edit the query:

1. Click a cell within the imported database table, the Table Tools Design tab should be available:

2. On the Table Tools Design tab, in the External Table Data group, click Properties. The External Data Properties menu opens:

Excel spreadsheet External Data Properties Dialog

3. Click . The Connection Properties dialog opens:

You can use the options on the Usage tab to control how often the data is refreshed.

4. Select the Definition tab:

The Connection String field defines which Fernhill SCADA the data is retrieved from. For example, you could change the Fernhill SCADA to a computer called RemoteServer by using this connection string:

Driver={Fernhill SCADA};Server=RemoteServer

You can edit the query in the Command Text field to change the data retrieved from Fernhill SCADA. To retrieve only the FullName and Value columns for a subset of the analog tags you could use this query:

SELECT FullName,Value
FROM "TagDB"."CommonBase"."TagDataPointAnalog"
WHERE FullName LIKE 'Main.RandomValues.%'

Further Information

Fernhill SCADA SQL

To learn about the SQL supported by Fernhill SCADA.

Setting up an ODBC Data Source Name (DSN)

To learn about configuring ODBC data Source Names.

Using ODBC Connection Strings to Connect to Fernhill SCADA

To learn about the keywords Fernhill SCADA supports in ODBC connection strings.

Fernhill SCADA ODBC Driver

To learn how the Fernhill SCADA ODBC Driver can provide data to other applications.

Glossary

For the meaning of terms used in Fernhill SCADA.