PowerAnt: Teach Your computer To drive The real World
PowerAnt is control from personal computer, using RS232 port, from 14 and more external electrical devices. Open, Human-Readable protocol, it is fully described in programmer's manual. Control of electrical equipment with power supply near 200W. You Wish control the real world from MS Excel?
 PowerAnt
Owerview
Applications
DataSheet
Documentation
Photos
 PowerAnt types
SwSe
SwSw
SeSe
 Programming
Perl samples
C/C++ - samples
MS Access 2000
MS Excel 2000
 Order
Order details
Price
 News RSS
 Contacts
Яндекс цитирования Каталог ресурсов ListTop.Ru Rambler's Top100

Programming PowerAnt in MS Excel 2000

PowerAnt can be programmed using standard component mscomm32.ocx. This component is created in Microsoft company but not included in any version of MS Windows. May be the reason of this is some errors. (Officially known bug: Memory leak in open/close of RS-232 port). Typically RS-232 port is open at start of the program and close at the end of program. It's make this bug is not so dangerous.

Download mscomm32.ocx (mscomm.zip, 51KB) unzip component and copy it to folder C:\Windows\System.

To tweak this component you must put some keys to MS Windows registry (In the Windows world we do not like easy ways;-)):

REGEDIT4

[HKEY_CLASSES_ROOT\Licenses\4250E830-6AC2-11cf-8ADB-00AA00C00905]
@ = "kjljvjjjoquqmjjjvpqqkqmqykypoqjquoun"
To do that - just double click in file mscomm.reg (stored into archive mscomm.zip). The RegEdit ask you - would you like to make some changes with System Registry? Your right answer is "Yes".

Run MS Excel. Open the Visual Basic toolbar (View/Tools/Visual Basic) and "Controls" toolbar (View/Tools/Controls). Switch to constructor mode (using Visual Basic toolbar). Create the CommandButton1 button. Push right mouse button on CommandButton1 and choice menu item "Source code". The Microsoft Visual Basic editor is opens.

Right this you need to do somesing magical in Microsoft Visual Basic editor. Open menu Tools/References. In window push the button Browse.... Find in filesystem mscomm32.ocx (folder C:\Windows\System). In the window "References" in pane "Available References" you can see checked string "Microsoft Comm Control 6.0".

Copy the followed program source in to editor:

Dim PwrAntComm As New MSComm

' When the button is pressed, the command 08?? is executed 
'	the device answer puts to cell C12
Private Sub CommandButton1_Click()
    ' 2 - COM port number (COM1 = 1, COM2 = 2, ... )
    PwrAntOpen (2)
    ' Ask device with address 08 "Who are you, and what you can do?"
    ' Put device answer to cell C12
    Sheet1.[C12] = PwrAntCmd("08", "??")
    Call PwrAntClose
End Sub

' Open RS-232 port, if this port is not be opened
' PortNumber - Number of COM port (COM1 = 1, COM2 = 2, ... )
Sub PwrAntOpen(PortNumber As Integer)
    If Not PwrAntComm.PortOpen = True Then
        PwrAntComm.CommPort = PortNumber
        PwrAntComm.Settings = "9600,N,8,1"
        PwrAntComm.Handshaking = comNone
        PwrAntComm.InputLen = 0
        PwrAntComm.InBufferSize = 40
        PwrAntComm.OutBufferSize = 40
        PwrAntComm.PortOpen = True
        PwrAntComm.RThreshold = 0
        PwrAntComm.Output = Chr(27)     ' Clear the receive buffer in all devices
    End If
End Sub

' Close RS-232 port, if this port be opened
Sub PwrAntClose()
  If PwrAntComm.PortOpen = True Then
    PwrAntComm.PortOpen = False
  End If
End Sub


' This function execute the given command and return the device answer.
'	If something wrong (you talk with nonexisting device) then 
'	function is return the empty string. Maximum command execution
'	time is limited to 2 sec.
Function PwrAntCmd(AntName As String, AntCmd As String) As String
    Dim AntInStr As String
    Dim TimeOut As Date
    ' Send command to device
    PwrAntComm.Output = AntName & AntCmd & Chr(13)
    ' Read string by bytes from device and wait byte with code 0x0D
    AntInStr = ""
    TimeOut = Time
    Do
        dummy = DoEvents()      ' Let system do something, we do not need freeze the system
        If PwrAntComm.InBufferCount > 0 Then
            AntInStr = AntInStr & PwrAntComm.Input
        End If
    Loop Until (Right(AntInStr, 1) = Chr(13)) Or (Time - TimeOut > 1)
    ' Remove 0x0D at the end of string
    AntInStr = Left(AntInStr, Len(AntInStr) - 1)
    PwrAntCmd = AntInStr
End Function
	

Save the program and close the Visual Basic editor

Turn off the constructor mode (in Visual Basic tool bar).

If you push the button, the command 08?? is executed, and device answer is putted to cell C12.

Pay attention, when you push the button, program open and close RS-232 port and some small memory leak may be happens.

PowerAnt is control from personal computer, using RS232 port, from 14 and more external electrical devices. Open, Human-Readable protocol, it is fully described in programmer's manual. Control of electrical equipment with power supply near 200W. You Wish control the real world from MS Excel?
  © 2004-2014 by  www.anthillsolutions.com