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.

HansHartl
Topic Author
Offline
Senior Member

More

05 Mar 2015 10:21 #2920 by HansHartl

Replied by HansHartl on topic Filling Excel cells with results.

I got it, there was an error in the libary.

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

HansHartl
Topic Author
Offline
Senior Member

More

05 Mar 2015 09:14 #2913 by HansHartl

Replied by HansHartl on topic Filling Excel cells with results.

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.

support
Offline
Moderator

More

05 Mar 2015 09:00 #2911 by support

Replied by support on topic Filling Excel cells with results.

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.

HansHartl
Topic Author
Offline
Senior Member

More

05 Mar 2015 08:04 #2909 by HansHartl

Replied by HansHartl on topic Filling Excel cells with results.

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.

support
Offline
Moderator

More

24 Feb 2015 19:32 #2851 by support

Replied by support on topic Filling Excel cells with results.

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:

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.

HansHartl
Topic Author
Offline
Senior Member

More

24 Feb 2015 10:26 - 24 Feb 2015 19:23 #2846 by HansHartl

Filling Excel cells with results. was created 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

Code:

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, vaclav.zaloudek

Time to create page: 0.147 seconds

Powered by Kunena Forum