Configure Excel

You can add real-time data from an OPC Systems Service into any local or remote Microsoft Excel Workbook for Excel 2003, 2007, and 2010.

Note: If you are using Microsoft Excel 2010 make sure to download and install the Microsoft Hotfix from the following link.

http://support.microsoft.com/kb/2405840

Follow the steps below for a simple setup for read and write functions in Microsoft Excel or view the following video.

https://www.opcsystems.com/opcexcel_video.htm

 

Step

Task

1

Start the OPC Excel tag browse application from the program group OPC Systems.NET.

2

Start Microsoft Excel.

3

In the OPC Excel tag browse application select the Local service or the remote service that has the OPC Systems.NET Tags you want to read and write to.

4

Browse for either an OPC Systems.NET Tag Value to read or DirectOPC item to read.

 

5

Select the Copy to Clipboard button.

 

6

Switch to Microsoft Excel and paste the formula into any cell and move off of the cell.

The value will appear in the cell.

 

7

To enable a write function back to OPC Systems.NET Tags or DirectOPC items return to the OPC Excel tab browser and select a tag you want to write to.

 

8

Under the Read / Write section select the Write option and specify either a value to write or a cell location in the Worksheet where the value will come from.

9

Select the Copy to Clipboard button.

 

10

Switch to Microsoft Excel and paste the formula into a cell.

=RTD("OPCExcel",,"Write OPC Output.Value","Write",B2,0.01,1)

 

11

Enter your desired value you want to write, in this example the data will be coming from the value in the cell B2.

Verify that the value has been written to the tag.  If the data source is an OPC Item the value will be written to the OPC Server.

 

12

If the service you are connected to has Security enabled for reading or writing to tags you can specify the Security option in the OPC Excel tag browser and specify the User Name and Password for the Log In option.

=RTD("OPCExcel",,"Security","Log In","","",1)

The User Name and Password can come from other cells that can be made hidden in the Excel Workbook if you like.

=RTD("OPCExcel",,"Security","Log In",D2,E2,1)

13

You can then save your Microsoft Excel Workbook and open anytime in the future to update the values.

 

14

If you plan to run the Workbook on a different PC than the OPC Systems Service include the Network Node Name, IP Address, or registered Internet domain name in the tag path.

Tag:

 \\www.opcsystemsserver.com\Ramp.Value

Formula:

=RTD("OPCExcel",,"\\www.opcsystemsserver.com\Ramp.Value","Read","?")

To run the Workbook remotely you will need to install the product feature OPC Excel.NET on that PC, but you only need a license of OPC Excel.NET on the data source PC where the OPC Systems Service is running.

More:

Hotfix for Excel 2010