- Posts: 5
- Thank you received: 0
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.
- Forum
- Discussions
- QuickOPC-Classic in COM
- Reading, Writing, Subscriptions, Property Access
- Filling Excel cells with results.
Filling Excel cells with results.
Please Log in or Create an account to join the conversation.
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.
Regarding
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.Is there any READXXX methode to get readings automatic for a special intervall ( for example every second)?
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.
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.
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.
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
Please Log in or Create an account to join the conversation.
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
Please Log in or Create an account to join the conversation.
- Forum
- Discussions
- QuickOPC-Classic in COM
- Reading, Writing, Subscriptions, Property Access
- Filling Excel cells with results.