Example of reading/writing data to serial port from Excel.
Enter the following code in the Excel Macro
–
module section:
To create a new module go to Excels “Insert” menu and select “Module”.
Global RowPointer As Long
Global Const CmdLine = "C:FilesPlusExe /MINIMIZED"
'CPS installation directory
Sub Auto_Open()
' this sub runs automatically when you open the spreadsheet
RowPointer = 1
RetVal = Shell(CmdLine, 4)
' launch CPS Plus using command line defined above
If RetVal = 0 Then
' error - CPS not found, check if CPS is installed in C:FilesPlus\par
Beep: MsgBox ("Cannot Find
CPS.Exe")
' display warning
Exit Sub
' quit if CPS not found
End If
' otherwise - if CPS is found
Application.Wait Now + 0.00001
' wait until CPS loads
AppActivate Application.Caption
' Activate excel again
GetSerialData
'set up excel to collect data from CPS
End Sub
Sub GetSerialData()
RowPointer = 1
'initialize global variable
Sheets("Sheet1").Activate
' activate sheet 1 and set up a DDE link to CPS Plus driver
Sheets("Sheet1").Cells(1, 50).Formula = "=CPSPLUS|DRIVER!NEWDATA"
Sheets("Sheet1").Cells(2, 50).Formula = "=CPSPLUS|DRIVER!NEWDATA"
ActiveWorkbook.SetLinkOnData "CPSPLUS|DRIVER!NEWDATA", "GetCPSData"
' the SetLinkOnData method causes excel to run the GetCPSData macro
'
automatically when new data is available in the CPS Plus.
End Sub
Sub GetCPSData()
On Error Resume Next
chan = DDEInitiate("CPSPLUS", "DRIVER")
F1 = DDERequest(chan, "COM1_VALUE")
' Read new data from CPS.
CPS$ = F1(1)
' convert F1(1) - variant array to a string
F2 = DDERequest(chan, "COM1_DATE_STAMP")
' read date stamp in format DD-MM-YY
'F2 = DDERequest(chan, "COM1_DATEA_STAMP") read date in format MM-DD-YY
CPS2$ = F2(1)
' convert F2(1) - variant array to a string
Sheets("Sheet1").Cells(RowPointer, 1).Formula = CPS$
Sheets("Sheet1").Cells(RowPointer, 2).Formula = CPS2$
RowPointer = RowPointer + 1
DDETerminate chan
End Sub
Sub Auto_Close()
' this macro runs automatically when you close the spreadsheet
StopReading
' set up Excel to stop reading data from CPS Plus
chan = DDEInitiate("CPSPLUS", "DRIVER")
DDEExecute chan, "[UnloadCPS]"
' Exits CPS Plus
DDETerminate chan
End Sub
Sub StopReading()
Sheets("Sheet1").Activate
' activate sheet1
Sheets("Sheet1").Cells(1, 50).Formula = ""
' remove the dde link
Sheets("Sheet1").Cells(2, 50).Formula = ""
' remove the dde link
ActiveWorkbook.SetLinkOnData "CPSPLUS|DRIVER!NEWDATA", ""
' Set last argument of SetLinkOnData to an empty string ("") to indicate that
' no procedure should run when the link is updated.
End Sub
Sub ExecCommand1()
‘
execute manual command 1
chan = DDEInitiate("CPSPLUS", "DRIVER")
DDEExecute chan, "[ExecCommand1]"
DDETerminate chan
End Sub
Sub SendSelfTest()
‘
send custom text and control characters to serial port
chan = DDEInitiate("CPSPLUS", "DRIVER")
DDEExecute chan, "[WriteToCOM1(SELFTEST 13)]"
DDETerminate chan
End Sub