Getting Started – Excel

Follow the steps below for a simple setup for read and write functions in Microsoft Excel or view the video in the Videos-Excel topic in this section.

These steps can be followed after downloading and installing OAS from the Support-Downloads page.

Step 1

Add the OASExcel Add-In to the operating systems Excel Add-Ins.  You will only need to do this once on the operating system where the Excel Workbooks will be used.

Start Microsoft Excel. and open an existing Workbook or new Workbook.

Select File-Options to open the Excel Options window.

Select Add-ins in the lower left and then the Go… button to the right of Manage Excel Add-ins.

Select the Browse button and browse to C:\Program Files (x86)\Open Automation Software\OPC Systems.NET\ to select OASExcel-AddIn.xll for 32 bit version of Excel or OASExcel-AddIn64.xll for 64 bit version of Excel and click OK.

Note: If you are uncertain which version of Excel you are running select File-Account and select the button labeled About Excel and you see either 32-bit or 64-bit at the top.  Also if the OASExcel formulas do not appear in the next steps you most likely need to select the alternative xll file.

You are now ready to use the OASExcel formulas to read and write tag values to any local or remote OAS Engine with a license of OAS Excel.

The Excel ThrottleInterval by default runs at 2 seconds in Excel.  We recommend to change this default ThrottleInterval.

Copied from https://news.cqg.com/blogs/2011/04/adjusting-rtd-interval-throttle-microsoft-excel

  1. In Excel, go to the Visual Basic Editor by pressing ALT + F11.
  2. On the Immediate Window at the bottom of the display, type this code:
    Application.RTD.ThrottleInterval=0
    (Note: If the Immediate Window is not open, press CTRL + G to display the window.)
  3. Make sure your cursor is on the line that you just typed and then press ENTER.
  4. To verify that it is set correctly, type this line of code on the Immediate Window:
    ? Application.RTD.ThrottleInterval
  5. Make sure your cursor is at the end of this line and then press ENTER. The window should display 0; then you know that your throttle interval is set correctly.

With the ThrottleInterval now set to 0 you will be able to achieve 30 millisecond updates from the OAS Engine.  You will only need to perform this once on the operating system.

Step 2

Use the OAS Excel Tag browse application to define read formulas to local and remote OAS Tags.

Start the OAS Excel tag browse application from the program group Open Automation Software.

If the Workbook will be deployed to a remote computer or the OAS Engine resides on another computer enter the IP Address, registered domain name, or network node name of the remote OAS Engine where the tag resides to read or write to and click on Select.

If on the same computer leave Network Node as localhost and click on Select.

A list of tags from the remote service will appear.

Select a tag you want to read or write to from the tag list.  In this example we will use OASRead function to read a tag value.

Notice that the formula =OASRead(“Ramp.Value”,”?”) appears at the bottom of the window.

Copy this formula and paste into a Cell in Excel to read a value continuously from the local or remote OAS Engine.

The value will appear if the local or remote tag has good quality.

Use Ramp2 tag as a simulated value that changes every 100 milliseconds or any other tag you like that is changing faster than 1 second to check the ThrottleInterval.

=OASRead(“Ramp2.Value”,”?”)

Step 3

Optionally write to OAS Tags from Excel.

If you want to write to an OAS Tag go back to the OAS Excel Tag browse application and select the local or remote tag you want to write to and select the Write radio button.  Enter either a fixed value in the Value to write field or enter a Cell location in the Data source cell location field.  In this example we will write the value from cell A1 to the tag Write Float.Value.

Note: You can specify a deadband value that will disable the write if the value read back is within the range specified.  This is useful when writing to single or double float values within a controller.  You can also use the last parameter of the OASWrite formula to enable or disable the write entirely.  This is useful to have all of the values preset in the workbook before writing the values to a controller, OPC Server, or other data source.

Copy the formula and paste into a Cell in Excel.  In this example it will be place in Cell B2.

The OASWrite function automatically reads the OAS Tag value to determine if there is success in writing the value with full confirmation coming back from the Data Source of the Tag.

Following are the return values from the OASWrite function.

0 – Disabled
1 – Equal
2 – Value is being written
3 – Bad quality
4 – Cannot type cast value
5 – Unknown value to type cast

If the source value matches the value to be written a return value will be 1.

Step 4

Implement security login

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

=OASLogin(“TheUser”,”ThePassword”)

The User Name and Password can come from other cells that can be made hidden in the Excel Workbook if you like.  In the following example the username and password would come from cells B10 and B11.

=OASLogin(B10,B11)

Step 5

Save the Excel Workbook.  Open anytime in the future and the read and write functions will perform.

Step 6

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

Tag:

\\www.opcsystems.com\Ramp.Value

Formula:

=OASRead(“\\www.opcsystems.com\Ramp.Value”,”?”)

To run the Workbook remotely you will need to install the product feature OAS Excel Connector or copy the following files to the new PC then perform Step 1 above to register the OASExcel-AddIn on that PC.

The following files can be copied from C:\Program Files (x86)\Open Automation Software\OPC Systems.NET\ to any directory on remote systems to register the OASExcel-AddIn.

  • OASExcel.dll
  • OASExcel-AddIn.dna
  • OASExcel-AddIn.xll
  • OASExcel-AddIn64.dna
  • OASExcel-AddIn64.xll
  • OPCSystemsInterface.dll

You only need a license of OAS Excel Connector on the data source PC where the OAS Service is running so you can run the Excel Workbooks remotely with an OAS Network License.