Professional OPC
Development Tools

logos

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.

Filling Excel cells with results.

More
05 Mar 2015 10:21 #2920 by HansHartl
I got it, there was an error in the libary.

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

More
05 Mar 2015 09:14 #2913 by HansHartl
In Your example UASubscribeToMultiple2_2015-02-24-1.xls I get an error for:
Public WithEvents Client As EasyUAClient . What libary is needed for this, and where can I get it?

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

More
05 Mar 2015 09:00 #2911 by support
Hello.

Regarding

Is there any READXXX methode to get readings automatic for a special intervall ( for example every second)?

In OPC, whenever you need to obtain values periodically, and provided that the consuming tool can support it, you are advised to use the so-called subscriptions, and not repeated Reads. This is for multiple reasons, one of them being the performance.

With QuickOPC and the OPC Data Access specification, which I think is your case, you will use methods like EasyDAClient.SubscribeItem or EasyDAClient.SubscribeMultipleItems to set up subscriptions. You will then receive notifications (the incoming values) via an event, for which you need to set up an event handler.

Regarding

And is there a way to write direct to a file (CSV or TXT)?


No, this is the application-specific processing that we are not including in the component.

Regarding

And I found out that the reading is faster when I do it every second, than every 10 seconds.


When you call Reads periodically and do not set up a subscription, we will open a connection to the server when it is needed - this takes some time. Then we do the "read", and keep the connection open for a "hold period", which by default is several seconds long (can be configured). What you are observing may be the consequence of the opening/closing mechanism. When you issue another Read fast enough, the connection stays open. When you wait longer, it is closed and then re-open, which slows down things.

Best regards

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

More
05 Mar 2015 08:04 #2909 by HansHartl
Is there any READXXX methode to get readings automatic for a special intervall ( for example every second)?
And is there a way to write direct to a file (CSV or TXT)?

And I found out that the reading is faster when I do it every second, than every 10 seconds.

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

More
24 Feb 2015 19:32 #2851 by support
There is a known problem with modifying Excel sheet from inside the COM callbacks. More about it further below. But, I do not quite understand your question "[can I] get the result value in an array,...". Please explain. You do not have to subscribe to the value changes; you can use one of the ReadXXXX methods, and get back the result (or array of results), and there won't be any callbacks which cause this error.

Now back to the original issue: I am attaching two Excel spreadsheet which show different approaches to the problem. They were made for OPC Unified Architecture, but can be modified to OPC "Classic" as well - let me know if you need help with it.

File Attachment:

File Name: UASubscrib...2-24.xls
File Size:54 KB


File Attachment:

File Name: UASubscrib...2-24.xls
File Size:63 KB


The first example uses a scheduled cell update, allowing edits while subscribed. Certain string values are problematic to transfer as macro arguments, though. The second example stores updates in an intermediate dictionary, for the same purpose. Perhaps that is an approach to take. Here is its code:
' Declare an EasyOPC-UA component
Public WithEvents Client As EasyUAClient
 
' Holds cells to be updated, and the update values.
Dim CellsToUpdate As New Dictionary
 
Private Sub SubscribeCommandButton_Click()
    ' Create the EasyOPC-UA component
    Set Client = New EasyUAClient
 
    Dim MonitoringParameters As New UAMonitoringParameters
    MonitoringParameters.SamplingInterval = 1000
 
    ' Define OPC node IDs
    ' For "states", we use names of cells where the values should be updated
 
    Dim MonitoredItemArguments1: Set MonitoredItemArguments1 = CreateObject("OpcLabs.EasyOpc.UA.OperationModel.EasyUAMonitoredItemArguments")
    MonitoredItemArguments1.EndpointDescriptor.UrlString = "http://localhost:51211/UA/SampleServer"
    MonitoredItemArguments1.NodeDescriptor.NodeId.ExpandedText = "nsu=http://test.org/UA/Data/;i=10845"
    Set MonitoredItemArguments1.MonitoringParameters = MonitoringParameters
    MonitoredItemArguments1.State = "D2"
 
    Dim MonitoredItemArguments2: Set MonitoredItemArguments2 = CreateObject("OpcLabs.EasyOpc.UA.OperationModel.EasyUAMonitoredItemArguments")
    MonitoredItemArguments2.EndpointDescriptor.UrlString = "http://localhost:51211/UA/SampleServer"
    MonitoredItemArguments2.NodeDescriptor.NodeId.ExpandedText = "nsu=http://test.org/UA/Data/;i=10853"
    Set MonitoredItemArguments2.MonitoringParameters = MonitoringParameters
    MonitoredItemArguments2.State = "D3"
 
    Dim MonitoredItemArguments3: Set MonitoredItemArguments3 = CreateObject("OpcLabs.EasyOpc.UA.OperationModel.EasyUAMonitoredItemArguments")
    MonitoredItemArguments3.EndpointDescriptor.UrlString = "http://localhost:51211/UA/SampleServer"
    MonitoredItemArguments3.NodeDescriptor.NodeId.ExpandedText = "nsu=http://test.org/UA/Data/;i=10855"
    Set MonitoredItemArguments3.MonitoringParameters = MonitoringParameters
    MonitoredItemArguments3.State = "D4"
 
    Dim arguments(2)
    Set arguments(0) = MonitoredItemArguments1
    Set arguments(1) = MonitoredItemArguments2
    Set arguments(2) = MonitoredItemArguments3
 
    ' Subscribe to OPC monitored items
    Call Client.SubscribeMultipleMonitoredItems(arguments)
End Sub
 
Private Sub UnsubscribeCommandButton_Click()
    ' Unsubscribe from all OPC monitored items
    Call Client.UnsubscribeAllMonitoredItems
End Sub
 
Private Sub Client_MonitoredItemChanged(ByVal Sender As Variant, ByVal E As OpcLabs_EasyOpcUA.EasyUAMonitoredItemChangedEventArgs)
    ' Store the value in the intermediate dictionary. The cell name is passed to us in the State property.
    Set CellsToUpdate(E.arguments.State) = E.AttributeData.Value
End Sub
 
Public Sub UpdateCells()
    ' Update values that have changed since the last update. The cell names and values are stored in the intermediate dictionary.
    For Each Cell In CellsToUpdate.Keys
        Range(Cell).Value = CellsToUpdate(Cell)
    Next
 
    ' Clear the intermediate dictionary.
    CellsToUpdate.RemoveAll
 
    ' Schedule next update.
    ThisWorkbook.ScheduleUpdate
End Sub

As I wrote, it is for OPC UA, but can be modified for OPC Classic. The idea is that when new values arrive, they are just stored into a data structure, while the actual cell updates happen on a scheduled timer which does not conflict with Excel internal sheet state.

Best regards
Attachments:

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

More
24 Feb 2015 10:26 - 24 Feb 2015 19:23 #2846 by HansHartl
According to your exsample I did following VBA:
But when I do something on the Excel sheet ( like marking cells) error 50290 comes up in
Range(varE.State).Value = varE.Vtq.Value.
Can I modify that I can get the result value in an array, to write into the cells by my own?

Thanks Hans
Dim cells_2(155) As String
    For i = 0 To 155
      cells_2(i) = "C" & Trim(Str(i + 11))
    Next i
 
 
 
    Dim cells_1(140) As String
    For i = 0 To 140
      cells_1(i) = "B" & Trim(Str(i + 8))
    Next i
 
 
 
 
 
    ' Subscribe to OPC items
    Call EasyDAClient1.SubscribeMultipleItems("", "KEPware.KEPServerEx.V4", item_1, 1000, cells_1)
End Sub
 
Private Sub UnsubscribeCommandButton_Click()
    ' Unsubscribe from all OPC items
    Call EasyDAClient1.UnsubscribeAllItems
End Sub
 
Private Sub EasyDAClient1_ItemChanged(ByVal varSender As Variant, ByVal varE As Variant)
    ' Update the value in the cell. The cell name is passed to us in the State property.
 
 
    Range(varE.State).Value = varE.Vtq.Value
 
 
End Sub
Last edit: 24 Feb 2015 19:23 by support. Reason: code formatting

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

Moderators: support
Time to create page: 0.056 seconds