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-UA in COM
- Reading, Writing, Subscriptions
- OPC UA VBA Com Object Connection Management
OPC UA VBA Com Object Connection Management
26 May 2022 09:52 #10900
by support
Replied by support on topic OPC UA VBA Com Object Connection Management
Hello.
I hunted this for 2 hours, and it really looked like that for some unknown reason, VBA does not accept the service name string that VBScript does.
But, in the end, I found that the cause was different: The service name is case sensitive, and you have a typo there as well.
The 'p' and 'c' in "OpcLabs" should be in lowercase.
Best regards
I hunted this for 2 hours, and it really looked like that for some unknown reason, VBA does not accept the service name string that VBScript does.
But, in the end, I found that the cause was different: The service name is case sensitive, and you have a typo there as well.
My VBScript example: "OpcLabs.EasyOpc.UA.Services.IEasyUAClientConnectionControl, OpcLabs.EasyOpcUA"
Your VBA code: "OPCLabs.EasyOpc.UA.Services.IEasyUAClientConnectionControl, OpcLabs.EasyOpcUA"Best regards
Please Log in or Create an account to join the conversation.
25 May 2022 19:03 #10897
by bpoulsen
Replied by bpoulsen on topic OPC UA VBA Com Object Connection Management
I typo'd when entering the error message on the post. Attached is a screenshot showing the actual error thrown by the call to GetServiceByName("OPCLabs.EasyOpc.UA.Services.IEasyUAClientConnectionControl, OpcLabs.EasyOpcUA")
Please Log in or Create an account to join the conversation.
25 May 2022 17:13 #10896
by support
Replied by support on topic OPC UA VBA Com Object Connection Management
Hello,
before I investigate deeper, how comes that there is the double "s" in "SServices" in "Error Message: Could not load type 'OPCLabs.EasyOpc.UA.SServices.IEasyUAClientConnectionControl' from assembly 'OpcLabs.EasyOpcUA'." ? Is that a typo in your post here, or is that really the precise text of the error message? The service name in the code that you posted, however, looks OK.
Regards
before I investigate deeper, how comes that there is the double "s" in "SServices" in "Error Message: Could not load type 'OPCLabs.EasyOpc.UA.SServices.IEasyUAClientConnectionControl' from assembly 'OpcLabs.EasyOpcUA'." ? Is that a typo in your post here, or is that really the precise text of the error message? The service name in the code that you posted, however, looks OK.
Regards
Please Log in or Create an account to join the conversation.
25 May 2022 16:44 - 25 May 2022 17:09 #10895
by bpoulsen
Replied by bpoulsen on topic OPC UA VBA Com Object Connection Management
Implementing this and only issue I'm having is the correct syntax for the call to EasyUAClient.GetServiceByName(). when i pass the string name as shown in your vbscript example I get the following error...
"Critical Error #-2146233054"
"Error Message: Could not load type 'OPCLabs.EasyOpc.UA.SServices.IEasyUAClientConnectionControl' from assembly 'OpcLabs.EasyOpcUA'."
I assume the literal string used in the vbScipt example needs to be changed when this method is implemented in VBA. Can you let me know the correct service name string to use in VBA?
Here's my VBA connection control code, error occurs in ua_ConnectToUaAServer() on the call to isg_OpcUaClient.GetServiceByName()...
"Critical Error #-2146233054"
"Error Message: Could not load type 'OPCLabs.EasyOpc.UA.SServices.IEasyUAClientConnectionControl' from assembly 'OpcLabs.EasyOpcUA'."
I assume the literal string used in the vbScipt example needs to be changed when this method is implemented in VBA. Can you let me know the correct service name string to use in VBA?
Here's my VBA connection control code, error occurs in ua_ConnectToUaAServer() on the call to isg_OpcUaClient.GetServiceByName()...
'******************************************************
'Implementation of all OPC UA Client functions used to read
'and write to the PLC OPC UA server (typically via a Softing UA Server Module)
'******************************************************
Option Explicit
Option Base 1
Dim action As Variant
Dim scrap As Variant
Public UaConnected As Boolean
Private isg_OpcUaClient As EasyUAClient ' OPC Labs Easy OPC UA Client
Private isg_UaClientConnectionControl As ComEasyUAClientConnectionControl 'used to lock connection to ua server while workbook is in open
Private isg_LockHandle As Long 'reference to OPC UA lock handle used to close connection later
Private opcUaServerURL As String
Private opcUaNamespace As String
Public Sub ua_ConnectToUaServer()
On Error GoTo ua_ConnectError
If UaConnected = False Then
If Not isg_OpcUaClient Is Nothing Then
'first disconnect existing instance
UaConnected = False
Call opc_ua.ua_DisconnectFromUaServer
End If
'set connection info from matrix registration worksheet
opcUaServerURL = ActiveWorkbook.Sheets("MatrixReg").Range("PLC_UA_URL").value
opcUaNamespace = ActiveWorkbook.Sheets("MatrixReg").Range("PLC_UA_NameSpace").value
'create instance and set client connection control lock
Set isg_OpcUaClient = New EasyUAClient
Set isg_UaClientConnectionControl = isg_OpcUaClient.GetServiceByName("OPCLabs.EasyOpc.UA.Services.IEasyUAClientConnectionControl, OpcLabs.EasyOpcUA")
isg_LockHandle = isg_UaClientConnectionControl.LockConnection(opcUaServerURL)
'issue an initial read to a generic tag to trigger the connection to the UA Server to open and lock a connection to the endpoint
scrap = isg_OpcUaClient.ReadValue(opcUaServerURL, "ns=" & opcUaNamespace & ";s=" & OPC_UA_TAGPREFIX & "DIConfig[0].delay")
UaConnected = True
Exit Sub
End If
ua_ConnectError:
action = ErrorHandler(Err)
Select Case action
Case RESUME_STATEMENT
Resume
Case RESUME_NEXT
Resume Next
Case UNRECOVERABLE
Call opc_ua.ua_DisconnectFromUaServer
Application.DisplayAlerts = True
End
Case Else
scrap = MsgBox("Critical Error #" & Format(Err) & Chr(13) & Chr(10) & _
"Error Message: " & Error(Err), vbOKOnly, "Unhandled OPC UA Exception")
Call opc_ua.ua_DisconnectFromUaServer
Application.DisplayAlerts = True
End
End Select
End Sub
Public Sub ua_DisconnectFromUaServer()
On Error Resume Next
If Not Workbooks("ISGProgramTool.xlam").Sheets("OPC_UA_VBA").isg_OpcUaClient Is Nothing Then
isg_UaClientConnectionControl.UnlockConnection (isg_LockHandle)
Set isg_OpcUaClient = Nothing
UaConnected = False
End If
End Sub
Last edit: 25 May 2022 17:09 by support. Reason: code formatting
Please Log in or Create an account to join the conversation.
25 May 2022 07:36 #10888
by support
Replied by support on topic OPC UA VBA Com Object Connection Management
Hello,
here is VBScript example for the same, which is closer to VBA.
It looks long, but that is only because it also hooks to "connection monitoring" in order to demonstrate what is happening under the hood. In reality, you need just these two or three parts:
and
and
Best regards
here is VBScript example for the same, which is closer to VBA.
Rem This example shows how to lock and unlock connections to an OPC UA server. The component attempts to keep the locked
Rem connections open, until unlocked.
Option Explicit
Dim endpointDescriptorUrlString: endpointDescriptorUrlString = "opc.tcp://opcua.demo-this.com:51210/UA/SampleServer"
Dim EndpointDescriptor: Set EndpointDescriptor = CreateObject("OpcLabs.EasyOpc.UA.UAEndpointDescriptor")
EndpointDescriptor.UrlString = endpointDescriptorUrlString
' Instantiate the client object.
Dim Client: Set Client = CreateObject("OpcLabs.EasyOpc.UA.EasyUAClient")
' Obtain the client connection monitoring service.
Dim ClientConnectionMonitoring: Set ClientConnectionMonitoring = Client.GetServiceByName("OpcLabs.EasyOpc.UA.Services.IEasyUAClientConnectionMonitoring, OpcLabs.EasyOpcUA")
If ClientConnectionMonitoring Is Nothing Then
WScript.Echo "The client connection monitoring service is not available."
WScript.Quit
End If
' Obtain the client connection control service.
Dim ClientConnectionControl: Set ClientConnectionControl = Client.GetServiceByName("OpcLabs.EasyOpc.UA.Services.IEasyUAClientConnectionControl, OpcLabs.EasyOpcUA")
If ClientConnectionControl Is Nothing Then
WScript.Echo "The client connection control service is not available."
WScript.Quit
End If
' Display the server condition changed events.
WScript.ConnectObject ClientConnectionMonitoring, "ClientConnectionMonitoring_"
WScript.Echo "Reading (1)"
' The first read will cause a connection to the server.
Dim AttributeData1: Set AttributeData1 = Client.Read(endpointDescriptorUrlString, "nsu=http://test.org/UA/Data/ ;i=10853")
WScript.Echo AttributeData1
WScript.Echo "Waiting for 10 seconds..."
' Since the connection is now not used for some time, and it is not locked, it will be closed.
WScript.Sleep 10*1000
WScript.Echo "Locking..."
' Locking the connection causes it to open, if possible.
Dim lockHandle: lockHandle = clientConnectionControl.LockConnection(EndpointDescriptor)
WScript.Echo "Waiting for 10 seconds..."
' The connection is locked, it will not be closed now.
WScript.Sleep 10*1000
WScript.Echo "Reading (2)"
' The second read, because it closely follows the first one, will reuse the connection that is already open.
Dim AttributeData2: Set AttributeData2 = Client.Read(endpointDescriptorUrlString, "nsu=http://test.org/UA/Data/ ;i=10853")
WScript.Echo AttributeData2
WScript.Echo "Waiting for 10 seconds..."
' The connection is still locked, it will not be closed now.
WScript.Sleep 10*1000
WScript.Echo "Unlocking..."
clientConnectionControl.UnlockConnection(lockHandle)
WScript.Echo "Waiting for 10 seconds..."
' After some delay, the connection will be closed, because there are no subscriptions to the server and no
' connection locks.
WScript.Sleep 10*1000
WScript.Echo "Finished."
Sub ClientConnectionMonitoring_ServerConditionChanged(Sender, e)
WScript.Echo e
End Sub
' Example output:
'
'Reading (1)
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Connecting; Success; Attempt #1
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Connected; Success
'-1.034588E+18 {Single} @2021-11-15T15:26:39.169 @@2021-11-15T15:26:39.169; Good
'Waiting for 10 seconds...
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Disconnecting; Success
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Disconnected(RetrialDelay=Infinite); Success
'Locking
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Connecting; Success; Attempt #1
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Connected; Success
'Waiting for 10 seconds...
'Reading (2)
'2.288872E+21 {Single} @2021-11-15T15:26:59.836 @@2021-11-15T15:26:59.836; Good
'Waiting for 10 seconds...
'Unlocking
'Waiting for 10 seconds...
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Disconnecting; Success
'"opc.tcp://opcua.demo-this.com:51210/UA/SampleServer" Disconnected(RetrialDelay=Infinite); Success
'Finished.It looks long, but that is only because it also hooks to "connection monitoring" in order to demonstrate what is happening under the hood. In reality, you need just these two or three parts:
' Obtain the client connection control service.
Dim ClientConnectionControl: Set ClientConnectionControl = Client.GetServiceByName("OpcLabs.EasyOpc.UA.Services.IEasyUAClientConnectionControl, OpcLabs.EasyOpcUA")' Locking the connection causes it to open, if possible.
Dim lockHandle: lockHandle = ClientConnectionControl.LockConnection(EndpointDescriptor)ClientConnectionControl.UnlockConnection(lockHandle)Please Log in or Create an account to join the conversation.
24 May 2022 18:06 #10886
by bpoulsen
Replied by bpoulsen on topic OPC UA VBA Com Object Connection Management
I think for my application the lock connection would be the way to go. I tend toward explicit coding as a default, especially in large complex applications like this one.
Can you provide some syntax guidance for instantiating and setting / clearing the lock property in VBA? I don't need a full example, just some code snippets would be very helpful.
Thanks!
Can you provide some syntax guidance for instantiating and setting / clearing the lock property in VBA? I don't need a full example, just some code snippets would be very helpful.
Thanks!
Please Log in or Create an account to join the conversation.
24 May 2022 17:48 #10885
by support
Replied by support on topic OPC UA VBA Com Object Connection Management
Hello.
QuickOPC keeps the connection open (and reuses it) for configurable time after the last operation (5 seconds is the default); it also keeps it open as long as there are any subscriptions. So, if you are doing the reads&writes in quick succession, your assumption (" I assume connecting to and then disconnecting from the OPC UA endpoint with each call") would be incorrect. But, if it the user initiating them, infrequently, then the connection could be closed and reopened and that can slow down the operations.
1) One way to deal with it is to increase the "hold period" to a larger value, potentially very large.
2) Another option is to "lock" the connection explicitly:
- opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...tion%20Control%20Services.html
- opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...20unlock%20a%20connection.html
Unfortunately there is no VBA example currently, but if you decided to go this way, we can prepare one if needed.
3) A "poor-man" option is also to subscribe to anything (a non-existent node would do), and keep the subscription (no need to process the incoming data) as long as you want the connection be maintained.
I hope this helps
QuickOPC keeps the connection open (and reuses it) for configurable time after the last operation (5 seconds is the default); it also keeps it open as long as there are any subscriptions. So, if you are doing the reads&writes in quick succession, your assumption (" I assume connecting to and then disconnecting from the OPC UA endpoint with each call") would be incorrect. But, if it the user initiating them, infrequently, then the connection could be closed and reopened and that can slow down the operations.
1) One way to deal with it is to increase the "hold period" to a larger value, potentially very large.
2) Another option is to "lock" the connection explicitly:
- opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...tion%20Control%20Services.html
- opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...20unlock%20a%20connection.html
Unfortunately there is no VBA example currently, but if you decided to go this way, we can prepare one if needed.
3) A "poor-man" option is also to subscribe to anything (a non-existent node would do), and keep the subscription (no need to process the incoming data) as long as you want the connection be maintained.
I hope this helps
Please Log in or Create an account to join the conversation.
24 May 2022 17:25 #10884
by bpoulsen
OPC UA VBA Com Object Connection Management was created by bpoulsen
I have an Excel application that supports several upload and download VBA functions that each move large amounts of data between the a single PLC OPC UA server endpoint and the Excel spreadsheet. These reads and writes are issued on demand from the Excel user. I do not need to subscribe to data or receive periodic updates as this is not a real time data application.
Based on the provided examples, I have been using the easyUAClient object’s read and write methods, but these are a bit slow and seem to have a lot of overhead, I assume connecting to and then disconnecting from the OPC UA endpoint with each call. During a given upload or download operation, my VBA code will potentially need to read thousands of tags and make many different calls to read and write operations as part of handshaking operations. I have been looking through the online documentation, but haven’t figured out the most efficient way to process these reads/writes.
I would prefer to make and maintain a single OPC UA Connection to a given endpoint and then be able to issue reads and writes on demand. It appears there is a built in hold period that will determine how long the easyUAClient will maintain a connection. I also have seen there is a UASmartSessionParameters Object in the API, but it is not clear how to utilize this in the VBA code for a given instance of an easyUAClient. Is there anyway to set the client to hold a connection indefinately until I issue a call to disconnect or set the client object reference to Nothing? Generally, can you give me some examples showing how I can control the client connection to the server in Excel VBA?
Thanks
Based on the provided examples, I have been using the easyUAClient object’s read and write methods, but these are a bit slow and seem to have a lot of overhead, I assume connecting to and then disconnecting from the OPC UA endpoint with each call. During a given upload or download operation, my VBA code will potentially need to read thousands of tags and make many different calls to read and write operations as part of handshaking operations. I have been looking through the online documentation, but haven’t figured out the most efficient way to process these reads/writes.
I would prefer to make and maintain a single OPC UA Connection to a given endpoint and then be able to issue reads and writes on demand. It appears there is a built in hold period that will determine how long the easyUAClient will maintain a connection. I also have seen there is a UASmartSessionParameters Object in the API, but it is not clear how to utilize this in the VBA code for a given instance of an easyUAClient. Is there anyway to set the client to hold a connection indefinately until I issue a call to disconnect or set the client object reference to Nothing? Generally, can you give me some examples showing how I can control the client connection to the server in Excel VBA?
Thanks
Please Log in or Create an account to join the conversation.
Moderators: support
- Forum
- Discussions
- QuickOPC-UA in COM
- Reading, Writing, Subscriptions
- OPC UA VBA Com Object Connection Management
Time to create page: 0.064 seconds