FAQs – Excel

Can you access data over the Internet in a local Excel Spreadsheet?
Yes, for both reading and writing.  View the following networking video on how to network to remote data services: Easy Networking How to network all products to central service using a fixed IP Address or registered domain name for the Internet. [videopop id="Y8jrVnuunWM"]
Can you protect remote data from being accessed from unauthorized use?
Yes, refer to the following Security video on how to setup user security. With the Excel Workbook use the OAS Excel Wizard with the Security option to add a security login function.  This is shown in Step 12 of the Excel - Getting Started section. Security Setup How to setup security for Open Automation Software in the service and in client applications. [videopop id="9PHq18fkxPU"]
Do I need a license for each remote Excel Workbook?
No, Open Automation Software is server based licensing and all licensing is enabled as the service to allow local and remote access for the number of concurrent licensed users activated.
I am running Microsoft Excel 2010, and I cannot get my data into the Excel Workbook.
Download the following Microsoft Hotfix for Excel 2010. https://support.microsoft.com/en-us/kb/2405840
What is the proper syntax for an RTD formula?
Use the OAS Excel Wizard found under the program group Open Automation Software. The following article describes RTD formulas in general: https://support.microsoft.com/en-us/kb/289150

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.

Step 1

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

Step 2

Start Microsoft Excel.

Step 3

In the OAS Excel tag browse application select the Local service or the remote service that has the Open Automation Software Tags you want to read and write to.

Step 4

Browse for either an Open Automation Software Tag Value to read or DirectOPC item to read.

Getting Started-Excel 428

Step 5

Select the Copy to Clipboard button.

Getting Started-Excel 429

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

Step 7

To enable a write function back to Open Automation Software Tags or DirectOPC items return to the OAS Excel tab browser and select a tag you want to write to.

Getting Started-Excel 430

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

Getting Started-Excel 431

Step 9

Select the Copy to Clipboard button.

Getting Started-Excel 429

Step 10

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

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

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

Step 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 OAS Excel tag browser and specify the User Name and Password for the Log In option.

Getting Started-Excel 432

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

Step 13

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

Step 14

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.opcsystemsserver.comRamp.Value

Formula:

=RTD(“OPCExcel”,,”\www.opcsystemsserver.comRamp.Value”,”Read”,”?”)

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

Excel

Software Licensing 43

Overview – Excel

The OAS Excel Connector product provides connectivity to Microsoft Excel Workbooks for both read and write functionality.

OAS Excel Connector can interface with Microsoft Excel 2003, 2007, and 2010.

Use the OAS Excel wizard application to define the RTD formulas to past into any cell.  Select Start-All Programs-OAS Excel.

Each licensed service for OAS Excel Connector can support unlimited local and remote Excel Workbooks. You can even have Excel Workbooks access data from OPC Servers over the Internet. All other data sources with their respective product features are also supported like OPC Clients, databases, and .NET applications.

Hotfix for Excel 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

More: