Use Excel as OPC Client for Freelance OPC Server
Hi,
Next week, i will go to the customer to replace DDE method by OPC method in MS-Excel VBA,
because DDE link do not exist anymore in Windows 7 with Freelance 9.2SP2.
So I tried this sample code as shown in help of CBF
section "Example of OPC access from MS-Excel and MS-Visual Basic.
with a right freelance project
Attribute VB_Name = "Module1"
Option Explicit 'to avoid that variables automatically
'variable declaration
Dim Server As IOPCServerDisp 'the reference to the server object
Dim ServerName As String ' name of the server'
Dim Group As IOPCItemMgtDisp 'the reference to the group object
Dim ServerUpdateRate As Long 'contains update rate, server is using for groups
Dim ServerGroupHandle As Long 'will contain the server generated group handle
Dim NrOfItems As Long 'number of items in this example
Dim ItemIDs() As String 'array for the item names
Dim ActiveStates() As Boolean 'array for the active states of each item
Dim ClientHandles() As Long 'array for the client handles of each item
Dim AccessPaths() As String 'array for the access path of each item
Dim RequestedDataTypes() As Integer 'array for the requested datatype of each item
Dim ItemErrors As Variant 'will contain the error code of each item
Dim ServerHandles As Variant 'will contain the server handle of each item
Dim ItemObjects As Variant 'will contain the references to each item
Sub OPCExampleMacro()
Dim i, ActualHour, ActualMinute, ActualSecond, WaitingTime 'local variables
NrOfItems = 1 'change this value if you want to read more items
'
'resize all arrays to the necessary size
'
ReDim ItemIDs(NrOfItems)
ReDim ActiveStates(NrOfItems)
ReDim ClientHandles(NrOfItems)
ReDim AccessPaths(NrOfItems)
ReDim RequestedDataTypes(NrOfItems)
'
' start the specified server, change the name for your configuration
'
ServerName = " ... " ‘ that is "Freelance2000OPCServer.86")
Set Server = CreateObject("ServerName")
'
' add a group to the server
'
Set Group = Server.AddGroup( _
"Group 1", _
True, _
1000, _
1, _
0, _
&H409, _
ServerGroupHandle, _
ServerUpdateRate)
'
'set the name of the item
'
ItemIDs(0) = "int01" 'change the name for your configuration
'
'set client handles and active state of the items
'
For i = 0 To (NrOfItems - 1)
ClientHandles(i) = i + 1 'use a different client handle for each item
ActiveStates(i) = True 'all items are active
Next
'
'add items to the group
Group.AddItems _
NrOfItems, _
ItemIDs, _
ActiveStates, _
ClientHandles, _
ServerHandles, _
ItemErrors, _
ItemObjects, _
AccessPaths, _
RequestedDataTypes
'as long as CTRL-BREAK is not pressed update every 2 seconds the sheet
'
Worksheets(1).Visible = True
While True
ActualHour = Hour(Now())
ActualMinute = Minute(Now())
ActualSecond = Second(Now()) + 2 'change 2 to x if you want faster execution
WaitingTime = TimeSerial(ActualHour, ActualMinute, ActualSecond)
Application.Wait WaitingTime
For i = 0 To (NrOfItems - 1)
Worksheets(1).Cells(1 + 2, i + 1) = "Server:"
Worksheets(1).Cells(1 + 2, i + 2) = ServerName
Worksheets(1).Cells(1 + 3, i + 1) = "Name:"
Worksheets(1).Cells(1 + 3, i + 2) = ItemObjects(i).ItemID
Worksheets(1).Cells(2 + 3, i + 1) = "Access Rights:"
Worksheets(1).Cells(2 + 3, i + 2) = ItemObjects(i).AccessRights
Worksheets(1).Cells(3 + 3, i + 1) = "Value:"
Worksheets(1).Cells(3 + 3, i + 2) =
Worksheets(1).Cells(4 + 3, i + 1) = "Quality:"
Worksheets(1).Cells(4 + 3, i + 2) = ItemObjects(i).Quality
Worksheets(1).Cells(5 + 3, i + 1) = "Timestamp:"
Worksheets(1).Cells(5 + 3, i + 2) = ItemObjects(i).Timestamp
Next
Wend
End Sub
Everything is working fine, I saw ItemObjects(i).Value is changed every 2 second.
I anwsered even access read is not used how ItemObjects(i).Value could be updated ?
But for me the Write access is more important for my customer.
So, I'm trying to find out how to make use of excel (VBA) in order to write values from my Freelance OPC DA (Data Access) Server.
(I tried to get IOPCSyncIO object to have Read/Write method by several ways but I always got failed...)
If anyone has got experience with this I'd appreciate the help.
Also if you have an excel template available, that'd be great, too.
Thanks
Answers
Hi,
MUCH THANK FOR YOUR HELP!!
Best Regards
Add new comment