FAWEB.NET / OPC Server - EXCEL Sample

	SITEMAP PRIVACY JAPANESE

JAPAN	HOME \| Product \| Support \| Knowledge \| Download \| Sales \| Company \| Contact

You are Here : HOME > Product > OPC Server > EXCEL Sample

BACK

EXCEL Sample

It introduces the easy sample program that Microsoft EXCEL accesses the OPC Server.
The sample program can be downloaded here.

NOTES
Our company does neither any operation guarantee nor the support for the sample code and each explanation that has been opened to the public to this site. The function is recommended to be verified enough when using it.

	Sample Display
The device value can be in real time monitored by setting the reference type to the cell of Excel. The above-mentioned example makes the topic named PLC1, and always reads the value of D0 by way of Ethernet.

Sample Code

Option Explicit
Option Base 1

Const MaxItemCount = 100
Dim WithEvents OPCMyserver As OPCServer
Dim WithEvents OPCMygroups As OPCGroups
Dim WithEvents OPCMygroup As OPCGroup
Dim OPCMyitems As OPCItems
Dim OPCMyitem As OPCItem
Dim ItemCount As Integer

Private Sub Class_Terminate()
	Dim Errors() As Long
	On Error Resume Next

OPCMygroup.IsActive = False
	OPCMygroups.Remove OPCMygroup.ServerHandle

Set OPCMyitems = Nothing
	Set OPCMyitem = Nothing
	Set OPCMygroups = Nothing
	Set OPCMygroup = Nothing

OPCMyserver.DisConnect
	Set OPCMyserver = Nothing
End Sub

Function Connect(ServerName As String)
	On Error GoTo ConnectError
	Set OPCMyserver = New OPCServer
	OPCMyserver.Connect ServerName, ""
	Connect = True
	Exit Function
ConnectError:
	Connect = False
End Function

Function AddGroup(Devicename As String, Devicecode As String, _
				Deviceno As Integer, point As Integer)
	On Error GoTo AddError
	Dim i As Integer
	Dim ItemServerHandles() As Long
	Dim ClientHandles(1) As Long
	Dim OPCItemIDs(1) As String
	Dim Errors() As Long

Set OPCMygroups = OPCMyserver.OPCGroups
	Set OPCMygroup = OPCMygroups.Add("Group1")
	OPCMygroup.UpdateRate = 1000
	Set OPCMyitems = OPCMygroup.OPCItems

ItemCount = point
	For i = 1 To ItemCount
		ClientHandles(1) = i
		If Devicecode = "X" Or Devicecode = "Y" Or _
					Devicecode = "B" Or Devicecode = "W" Then
			OPCItemIDs(1) = Devicename + "." + _
					Devicecode + Hex$(Val("&h" + Deviceno) + i - 1)
		Else
			OPCItemIDs(1) = Devicename + "." + _
					Devicecode + Format$(Val(Deviceno) + i - 1)
		End If
		OPCMyitems.AddItems 1, OPCItemIDs, ClientHandles, _
			ItemServerHandles, Errors ''', RequestedDataTypes, AccessPaths
		Cells(i, 1) = OPCItemIDs(1)
		If Errors(1) <> 0 Then
			Cells(i, 2) = "#N/A"
		End If
	Next i

AddGroup = True
	Exit Function
AddError:
	AddGroup = False
End Function

Sub Advise(bAdvise As Boolean)
	On Error Resume Next
	OPCMygroup.IsActive = bAdvise
	OPCMygroup.IsSubscribed = bAdvise
	If bAdvise = True Then
		Dim anItem As OPCItem
		For Each anItem In OPCMygroup.OPCItems
			anItem.Read OPCCache ', value, qual, time
			Cells(anItem.ClientHandle, 2) = anItem.Value
			Cells(anItem.ClientHandle, 3) = anItem.TimeStamp
			Cells(anItem.ClientHandle, 4) = anItem.Quality
			Set anItem = Nothing
		Next anItem
	End If
End Sub

Sub UnAdvise()
	OPCMygroup.IsSubscribed = False
End Sub

Sub SyncWrite()
	On Error Resume Next
	Dim anItem As OPCItem
	For Each anItem In OPCMygroup.OPCItems
		anItem.Write Cells(anItem.ClientHandle, 2)
		Set anItem = Nothing
	Next anItem
End Sub

Private Sub OPCMygroup_DataChange(ByVal TransactionID As Long, _
			ByVal NumItems As Long, ClientHandles() As Long, _
			ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date)
	Dim i As Integer
	For i = 1 To NumItems
	   Cells(ClientHandles(i), 2) = ItemValues(i)
	   Cells(ClientHandles(i), 3) = TimeStamps(i)
	   Cells(ClientHandles(i), 4) = Qualities(i)
	Next i
End Sub

Private Sub OPCMyserver_ServerShutDown(ByVal Reason As String)
	MsgBox "Server shutdown"
End Sub

BACK	Copyright © 1998-2006 TAKEBISHI Corporation All rights reserved.

TAKEBISHI Corporation
29 Mamedacho Nishikyogoku Ukyoku Kyoto 615-8501, JAPAN
TEL: +81-75-325-2171 / FAX: +81-75-325-2273 fa-support@takebishi.co.jp