Systems Engineering 
     Subsea Control Systems 
     Software Development 
     Products and Applications 
     Tech Info 
     Contacts & Key Personnel 
      Software Development

     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.

    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:

    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]
    User = Process IT Development
    NewExcelInstance = false
    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]
    Server   = ProcessIT.SimulationSvr.1
    Database = OpcSimClient.csv
    Sheet = Sheet3
    ; 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]

    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.

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

    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.

    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]

    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.

    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
            nValue = Sheet1.Range(strCell) + 1
            If nValue > 99 Then
                nValue = 0
            End If
            Sheet1.Range(strCell) = nValue
        End If
    End Sub

    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:

    True = WAHR
    False = FALSCH

    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:

    The file itself is a regular comma-delimited file. As an example, the first 4 lines and last 6 lines of the sample database definition file (Excel.csv) are shown here:
    OPC Tag,Type,Sheet,Item,Write

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

    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:
      Single precision floating point (32 bits)R4, Real
      Double precision floating point (64 bits)R8, Double
      StringBSTR, String
      BooleanBOOL, Boolean
      16 bit signed integerI2, Integer
      32 bit signed integerI4, Long
      Date or DateTimeDate, DateTime

    The name of the sheet.

    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.

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


    Inbuilt OPC Server Tags

    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:

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