• Distributed Control Systems
• PLCs
• ESD/PSD Systems
• Consulting

• Integrated Subsea Control Systems
• OPC Servers
• Valve Profiles
• Consulting
• Well Simulation

• EB-Access Utility
• Excel OPC Server
• Excel Bridge
• WITS Level 0 OPC Server
• TDC Report Capture Service
• PHD Watchdog Service

• Process Trend
• Control Loop Monitoring
• Gas Compressibility Server
• Gas Compressibility Web Service
• ZCalc Service
• Sequence Framework

• Technical Support
• OPC Simulation
• Downloads

• Norway
• United Kingdom

 Excel OPC Server 
Microsoft Excel is a powerful tool for data analysis and reporting. The Process IT Development Excel OPC Server is a combined OPC Server and OPC Client, designed to serve data from Excel spreadsheets.

It provides the following functionality:

• OPC DA 3.0 Server
  The OPC Server supports reading and writing of Excel data. There is a one-to-one mapping between an OPC item and an Excel Sheet/Cell.
• Macro Execution
  The OPC server can be configured to run macros defined in the Excel spreadsheet on a regular basis.
• Built-In OPC Client
  The calculations performed in a spreadsheet will often use live process data from the plant. The built-in OPC client allows for very efficient updating of cells in the spreadsheet from OPC data residing in DCS or SCADA system.

 Overview 
A typical setup for the Excel OPC Server is shown below. In this configuration, the Excel OPC Server acquires data from the DCS/SCADA system itself. There are two advantages with the use of this setup:

1. There is no need to write from the DCS/SCADA system to the Excel OPC server.
2. Updates via the Excel OPC Server's Built-In OPC Client occur on data change, which is a very efficient mechanism.

Another possible configuration for the Excel OPC Server is shown below. Here the Excel OPC Server acquires data from another (third-party) OPC Server, not connected to the main DCS/SCADA system.

As with the previous configuration, the advantages of this approach are that there is no need to write data to the Excel OPC Server, and in addition, any data changes from the third-party OPC Server to the Excel OPC Server are performed by exception.

Note that we do not recommend the use of Excel for process control purposes. It is for this reason that the OPC client component of the OPC server is designed to be read-only.

 
 Macro Execution 
The Macro Execution facility allows the user to define a number of macros for periodic execution.

The number of macros is defined using a record in the INI file, for example
Macros = 2

The macro name and the execution period (in seconds) is then defined in a separate section of the INI file, such as [Macro 1].

Note that the OPC Server does not limit the number of macros that the user can define. There will, of course, be a practical limit dependent on the complexity of the macros, and this limit can really only be determined through testing.

 
 Application Setup 
The Excel OPC Server is installed using the following download:

• Excel OPC Server Installer.

After running the Microsoft Installer package for the Excel OPC Server, run the batch file RegisterOPC.bat to register the OPC server, or alternatively run the following command from the command prompt:

ExcelOpcServer.exe   regserver

The OPC Server is configured using an initialisation file, a sample of which is shown below:

[Excel OPC Server]
LicenseKey=Demo
User = Process IT Development

NewExcelInstance = false
Database=Excel.csv

Visible=True
Workbook=C:\Program Files\Process IT\Excel Server\Test.xls

Macros = 2

[Macro 1]
Name = IncrementCellM1
Period = 1

[Macro 2]
Name = IncrementCellM2
Period = 5

[OPC Client]
Node=
Server   = ProcessIT.SimulationSvr.1
Database = OpcSimClient.csv
Sheet = Sheet3

[Excel]
; For non-English versions, set the True and False strings (e.g. German)
;True = WAHR
;False = FALSCH

; Disable AutoCalc when writing (200 milliseconds)
DisableAutoCalcOnWriteTime = 200

[Excel OPC Server]

LicenseKey
If the application has been licensed, this entry will be a license key string. If the license key is missing or incorrect (or "DEMO" as in the sample) then the OPC server will shut down after 1 hour.

User
This is the name of the person or company to which the OPC server has been licensed.

NewExcelInstance
This directive instructs the OPC Server whether it should use start up a new instance of Excel, or whether, if Excel is already running, it should use the existing instance.

If the OPC client is on the same PC as the Excel OPC Server, then use of the NewExcelInstance directive is straightforward.

If, however, the OPC client is on another PC (and therefore the Excel OPC Server is being instantiated using DCOM), then this directive needs to be considered in light of the DCOM settings.

For example, you have your OPC client on machine B, and want to connect to an Excel spreadsheet on machine A, which is currently open by the user logged into machine A.

The Excel OPC Server would therefore be installed on machine A, and in DCOM, under Identity, it would be set to run as the Interactive User.

Workbook
This is the name of the Excel Workbook that is to be used. Note that the Excel OPC Server supports one Workbook, although this may consist of several sheets.

 
[Macro N]

Name
This is the name of the macro, and is the same as the name of the subroutine in the VBA code module. Note that the subroutine needs to be Public, which is the default.

Period
This is the period, in seconds, at which the Excel OPC Server will run the macro.

In the example here, subroutine IncrementCellM1() will get called every second (Period = 1). This subroutine increments the value of Sheet1, Cell M1.

'--------------------
Sub IncrementCellM1()
'--------------------
    Dim nValue As Long
    Const strCell As String = "M1"
    
    If IsEmpty(Sheet1.Range(strCell)) Then
        Sheet1.Range(strCell) = 0
    Else
        nValue = Sheet1.Range(strCell) + 1
        If nValue > 99 Then
            nValue = 0
        End If
        Sheet1.Range(strCell) = nValue
    End If
    
End Sub

 
[Excel]
In this section it is possible to customize your installation.

True and False for Non-English Versions of Excel
When using Boolean data, it is necessary to define the text strings used for representing True and False. For example, if using German:

[Excel]
True = WAHR
False = FALSCH

DisableAutoCalcOnWriteTime
To avoid unnecessary calculations in Excel, it can be useful to disable autocalculation when writing data. If specified, this parameter is the length of time in milliseconds that AutoCalc is disabled when performing a write. The default is that AutoCalc is not disabled during a write.

Note that it is also possible to manipulate the Excel AutoCalc setting directly using the Excel.CalculationMode OPC tag.

 OPC Server Database 
The mapping of cells in the spreadsheet to OPC items is done in the CSV database definition file, the name of which is defined in the ExcelOpcSvr.ini file:

Database=Excel.csv

OPC Tag,Type,Sheet,Item,Write
Tag001.PV,R4,Sheet1,R1C11,x
Tag002.PV,R4,Sheet1,R2C11,x
Tag003.PV,R4,Sheet1,R3C11,x
...
Tag201.PV,R8,Sheet1,R4C14,x
Tag202.PV,I2,Sheet1,R5C14,x
Tag203.PV,I4,Sheet1,R6C14,x
Tag204.PV,BOOL,Sheet1,R7C14,x
Tag205.PV,String,Sheet1,R8C14,x
Tag206.PV,Date,Sheet1,R9C14,x

OPC Tag
The name for the OPC item. The OPC server defaults to the period (.) as the separator when browsing the server namespace.

Type
Specifies the data type that the OPC item will have. If the data in the Excel cell is of a different type, the data is converted to the type that is specified here.

The following data types are supported:

	Type	Entry
	Single precision floating point (32 bits)	R4, Real
	Double precision floating point (64 bits)	R8, Double
	String	BSTR, String
	Boolean	BOOL, Boolean
	16 bit signed integer	I2, Integer
	32 bit signed integer	I4, Long
	Date or DateTime	Date, DateTime

Sheet
The name of the sheet.

Item
The row-column reference for the cell.

For non-English versions of Excel it is necessary to change the cell row and column references to the local language. For example, the cell R1C4 would be given by Z1S4 when using a German version of Excel.

Write
Use any character in this column to indicate that it is permitted to write to the cell via OPC.

Inbuilt OPC Server Tags

Excel.CalculationMode
This allows you to write to the Excel Calculation Mode parameter. This is the same as using Tools-->Options-->Calculations on Excel. The values for Excel.CalculationMode are:

	0	xlCalculationManual
	1	xlCalculationSemiautomatic
	2	xlCalculationAutomatic

 
 Licensing 
The Excel OPC server is licensed on a per-machine basis. A license can be purchased via Share*It or Paypal:

	Share*It	Product 300101309
	Paypal

Please remember to include the name of the registered user of the OPC server, along with the name of the machine that the software will be running on.

Alternatively, send an email with these details to the tech support email address below.

 
 Problems or Questions 
Please get in touch by telephone or email in the event of any problems with the installation or operation of the OPC Server.
Tel: +47 99724942

Email: