Online Forums

Technical support is provided through Support Forums below. Anybody can view them; you need to Register/Login to our site (see links in upper right corner) in order to Post questions. You do not have to be a licensed user of our product.

Please read Rules for forum posts before reporting your issue or asking a question. OPC Labs team is actively monitoring the forums, and replies as soon as possible. Various technical information can also be found in our Knowledge Base. For your convenience, we have also assembled a Frequently Asked Questions page.

Do not use the Contact page for technical issues.

RTD command not working with Microsoft Excel 2016

1
2

slabbe
Topic Author
Offline
Premium Member

More

13 Sep 2018 19:19 #6644 by slabbe

Replied by slabbe on topic RTD command not working with Microsoft Excel 2016

Hi,

You pointed out in the right direction. Changing the separator fixed the formula error. Maybe instead of detecting the separator, it could simply be an advanced option that would allow to select the separator to generate in the RTD command.

But now, I have another issue. When I paste the formula in Excel, an press Enter, I do not have the formula error anymore, but I see a 0 in the box for 1 or 2 seconds, and then it disappear to leave the box empty.

I also tested with a dynamic variable taken from your opcua.demo-this.com example server, and I get the same exact behavior.

In both cases (my server and your example server) the 'Live Point Data' window, I see the data value properly, with a good status code.

Any idea what might going on?

Please Log in or Create an account to join the conversation.

support
Away
Moderator

More

13 Sep 2018 14:26 - 13 Sep 2018 14:27 #6643 by support

Replied by support on topic RTD command not working with Microsoft Excel 2016

Your argument separator is probably a semicolon (';'). Even if you cannot change it, you might be able to verify that, see e.g. lockone.wordpress.com/2015/08/06/excel-now-using-semicolons-...eparating-formulas-fix-it-now/ .

After figuring out what your list separator is, you may fix the formulas generated from Connectivity Explorer manually.

I will make a note to see whether, in a future version, we can somehow detect this, and align the generated formula syntax with what the current settings are. The problem here is that it is not guaranteed that the "recipient" is an always the Excel application residing on the same computer.

Regards

Last edit: 13 Sep 2018 14:27 by support.

Please Log in or Create an account to join the conversation.

slabbe
Topic Author
Offline
Premium Member

More

13 Sep 2018 13:17 #6642 by slabbe

Replied by slabbe on topic RTD command not working with Microsoft Excel 2016

Hi,

Thanks for the quick reply. Not sure why but my Windows 10 recently switched to french after an update (I am located in Quebec/Canada) so I think it is a good clue. My Office 360 is configured in french too, and I do not seem to have control on the regional settings.

Indeed, the =LEFT("Hello", 3) command gives the following error.

I will check what I can do with the IT guys here and keep you updated

Please Log in or Create an account to join the conversation.

support
Away
Moderator

More

12 Sep 2018 20:17 #6638 by support

Replied by support on topic RTD command not working with Microsoft Excel 2016

Hello,

I understand - but it's weird, though. I have quickly tested now with Excel 2016 and pasted your formula into it, and it did not give the error.

Is that on a system that is English-only, or isn't there some regional setting that affects the formula syntax - such as the argument separators (commas), etc.?

Can you show a picture of some other formula that does work - and has at least two parameters, ideally also some being strings? For example, does this work?

=LEFT("Hello", 3)

Thank you

Please Log in or Create an account to join the conversation.

slabbe
Topic Author
Offline
Premium Member

More

12 Sep 2018 19:51 #6637 by slabbe

RTD command not working with Microsoft Excel 2016 was created by slabbe

Hi,

I downloaded your QuickOPC as a trial version to test the OPC-UA feature with Excel. We have some customers that would be interested into using your software but I am not able to make it work. I have Microsoft Excel 2016 (Office 360). Basically I am following this tutorial:

opclabs.doc-that.com/files/onlinedocs/QuickOpc/2018.2/User%2...frame.html#Excel%20Option.html

I installed the software with the Excel option. I am able to add my OPC-UA server, browse through the data points ans add one of them into the Live Point data, to see the value. No problem here.

If I right click on the value and click 'Copy', I have the following command into my clipboard:

=RTD("OpcLabs.Office.Excel.ConnectivityRtdServer", , "opcuaattribute", "opc.tcp://192.168.3.240:4840/", "nsu=urn:OPAL_OPC_UA;ns=1;s=DoubleToClient", "", "", "Value;""""")

But if I paste it into the command box of one excel box, into my spreadsheet, I have an error from Excel that mentions that there is a problem with the format of my formula.

As an example, you will have the same error message if you type =; + Enter in one of the spreadsheet box formula.

What is wrong with the command that ends-up in the clipboard?

See screenshots attached.