A
alannfam
Hello,
Does any one out there have any experience with The CPS PLUS
serial data acquisition program?
I am trying to read data from a com port and put it into an Excel
spread sheet. I want to use CPS Plus because it is cheaper than the
WINWEDGE program and if i can get it to work i will be sending licensed
versions to several clients.
I am going in to an excel file cps.xls and into tools, then,macros,
then opening modules, and writing this code below into different
modules.
what i want to do is:::
i want to open the cps.xls file but i never want to write data to it,
as soon as i open it i want to open another file using this code:
Global Filename$
Sub AutoName()
' NameCheck Macro
Static StrLenDate, StrLenTime
SysDate$ = Date
SysTime$ = Time
StrLenDate = Len(SysDate$)
StrLenTime = Len(SysTime$)
A$ = "A"
P$ = "P"
Ms$ = "M"
Spaces$ = Chr(32)
Slashs$ = Chr(47)
Colons$ = Chr(58)
' Strip out slashes & spaces from the date
For j = 1 To StrLenDate
StringBit$ = Mid$(SysDate$, j, 1)
If StringBit$ = Spaces$ Then
StringBit$ = "-"
Mid$(SysDate$, j, 1) = StringBit$
End If
If StringBit$ = Slashs$ Then
StringBit$ = "-"
Mid$(SysDate$, j, 1) = StringBit$
End If
Next j
' Add a space to end of date
SysDate$ = SysDate$ + Spaces$
' strip out colon from the time
For k = 1 To StrLenTime
StringBit$ = Mid$(SysTime$, k, 1)
If StringBit$ = Colons$ Then
StringBit$ = " "
Mid$(SysTime$, k, 1) = StringBit$
End If
Next k
'Find the seconds portion of the time and strip it out
StringByte$ = Right(SysTime$, 5)
For M = 1 To 5
StringBit$ = Mid$(StringByte$, M, 1)
If StringBit$ = A$ Then
TimeofDay$ = A$ + Ms$ ' For AM
ElseIf StringBit$ = P$ Then
TimeofDay$ = TimeofDay$ + P$ + Ms$ 'For PM
Else
Mid$(StringByte$, M, 1) = ""
End If
Next M
'replace into time
TimeLen = Len(SysTime$)
SysTime$ = Mid$(SysTime$, 1, TimeLen - 5)
Filename$ = SysDate$ + SysTime$ + TimeofDay$
ActiveWorkbook.SaveAs (Filename$)
'This saves the file with the date and time as the filename
End Sub
Then i want to , write what ever data comes in from the comport to the
first row of this new file.
I got this to work. Now when the next line of data comes in , I need to
check the first 2 characters(this is the PartID#), if these 2
characters are the same as the last line of data's first 2 characters
then i just write this new line of data into the next row of the spread
sheet.But,if these first 2 characters are different than the the data
from the first line, then i need to open yet another file and put this
newdata in to it, that is where the problem starts, I am using
this code to do this:
Sub End_of_Log()
'
If PtID$ <> PrevPtID$ Then
ActiveWorkbook.Save
Workbooks.Open "C:\cps\cps.xls" 'cps
' Call auto_open
'activeworkbook.runautomacro xlautoopen
' call Autoname
ThisWorkbook.Close
End If
End Sub
*******
*******
When i do this it just opens up the cps.xls and it does not
get any new data
if i use the Call Auto_open or activeworkbook.runautomacro...,
it gives me the error
"the macro c:\cps.xls'|CPS_GetCOM1Data' cannot be found
if i call autoname i get different errors.
Does Any one know why or what to do??
********
*******
here is the rest of the Visual Basic code in the modules
' Functions:
' + LinkSerialPortToExcelSheet
' + BreakLinkSerialPortToExcelSheet
' + CPS_OpenPort
' + CPS_ClosePort
' + CPS_Unload
Type GenericLinkHolderType
RPointer(12) As Integer
SheetName(12) As String
Column(12) As Integer
End Type
Global PtID$,prevPtID$
Public GenericLinkHolder As GenericLinkHolderType
___________________________________
Sub Aut
pen() ' this sub runs automatically when you open the
spreadsheet
LinkSerialPortToExcelSheet 1, "Sheet1", 1
' link from RS232 port COM1, to Sheet1 - column 1
End Sub
_________________________________
Sub Auto_Close() ' this macro runs automatically when you close the
spreadsheet
' Set up excel to stop reading data from CPS Plus / RS232 ports
BreakLinkSerialPortToExcelSheet 1, "Sheet1", 1
End Sub
__________________________________
Public Sub LinkSerialPortToExcelSheet(com_port_number As Integer,
Sheet_Name As String, Column_number As Integer)
On Error GoTo NoStartCPSPlus
If Not LinkSPortToExcelSheet(com_port_number, Sheet_Name,
Column_number) Then
End If
Exit Sub
NoStartCPSPlus:
MsgBox "DDE Connecion to CPS Plus application failed. Please start CPS
Plus manually and restart Excel."
End Sub
_________________________
Function LinkSPortToExcelSheet(com_port_number As Integer, Sheet_Name
As String, Column_number As Integer) As Boolean
On Error GoTo errhandler
Dim CallbackFunction As String
Dim CPS_DDELink As String
GenericLinkHolder.RPointer(com_port_number) = 1
GenericLinkHolder.SheetName(com_port_number) = Sheet_Name
GenericLinkHolder.Column(com_port_number) = Column_number
AppActivate Application.Caption ' Activate excel
Sheets(Sheet_Name).Activate
' activate sheet 1 and set up a DDE link to CPS Plus driver
CPS_DDELink = "CPSPLUS|DRIVER!COM" & CStr(com_port_number)
Sheets(Sheet_Name).Cells(Column_number, 50).Formula = "=" &
CPS_DDELink ' establish a DDE link
CallbackFunction = "CPS_GetCOM" & CStr(com_port_number) & "Data"
ThisWorkbook.SetLinkOnData CPS_DDELink, CallbackFunction
LinkSPortToExcelSheet = True
Exit Function
errhandler:
LinkSPortToExcelSheet = False
End Function
____________________________
Public Sub BreakLinkSerialPortToExcelSheet(com_port_number As Integer,
Sheet_Name As String, Column_number As Integer)
On Error Resume Next
Dim CPS_DDELink As String
AppActivate Application.Caption ' Activate excel
Sheets(Sheet_Name).Activate ' activate sheet 1
Sheets(Sheet_Name).Cells(Column_number, 50).Formula = "" ' remove the
dde link
CPS_DDELink = "CPSPLUS|DRIVER!COM" & CStr(com_port_number)
ThisWorkbook.SetLinkOnData CPS_DDELink, ""
End Sub
_________________________---
Private Sub CPS_GetCOM1Data()
' Type: Internal
' Reads data from serial RS232 port 1 - COM1.
' Use LinkSerialPortToExcelSheet to setup column and Sheet
On Error Resume Next
Dim prt_num As Integer
prt_num = 1 ' for COM1
Dim Com1Data As String
Dim rawnum As Integer
Dim F1 As Variant
Dim lbnd As Integer
Dim ubnd As Integer
chan = DDEInitiate("CPSPLUS", "COM1")
F1 = DDERequest(chan, "COM1DATA") ' get RS232 data from serial port
COM1 into Excel temp variable F1.
lbnd = LBound(F1)
ubnd = UBound(F1)
If lbnd = ubnd Then
Com1Data = CStr(F1(1))
' convert F1(1) - variant array to a string
If Len(Com1Data) > 0 Then
Call AutoName
Call End_of_log
PtID$ = Trim$(Mid$(Com1data, 2, 2)) ' start with position 2
Sheets(GenericLinkHolder.SheetName(prt_num)).Cells(GenericLinkHolder.RPointer(prt_num),
GenericLinkHolder.Column(prt_num)).Formula = Com1Data
GenericLinkHolder.RPointer(prt_num) =
GenericLinkHolder.RPointer(prt_num) + 1
End If
Else
For lbnd = 1 To ubnd
Com1Data = CStr(F1(lbnd))
' convert F1 - variant array to a string
If Len(Com1Data) > 0 Then
Call AutoName
Call End_of_log
PtID$ = Trim$(Mid$(Com1data, 2, 2)) ' start with position 2 because
position 1 is an asterisk
Sheets(GenericLinkHolder.SheetName(prt_num)).Cells
(GenericLinkHolder.RPointer(prt_num),
GenericLinkHolder.Column(prt_num)).Formula = Com1Data
GenericLinkHolder.RPointer(prt_num) =
GenericLinkHolder.RPointer(prt_num) + 1
End If
Next
End If
PrevPtID$ = PtID$
'Sheet1.Range("A65536").End(xlUp).Offset(1, 0).Value = Com1Data
DDETerminate chan
End Sub
--___________________________________________
____________________________________________
'not using this function yet
Public Function CPS_OpenPort(port_number As Integer) As Boolean
Dim chan
Dim ComPortName As String
On Error GoTo failopenport
ComPortName = "COM" + CStr(port_number)
chan = DDEInitiate("CPSPLUS", ComPortName)
DDEExecute chan, "[OPEN]"
DDETerminate chan
CPS_OpenPort = True
Exit Function
failopenport:
CPS_OpenPort = False
End Function
____________________________
'not using this function yet
Public Function CPS_ClosePort(port_number As Integer) As Boolean
Dim chan
Dim ComPortName As String
On Error GoTo failcloseport
ComPortName = "COM" + CStr(port_number)
chan = DDEInitiate("CPSPLUS", ComPortName)
DDEExecute chan, "[CLOSE]"
DDETerminate chan
CPS_ClosePort = True
Exit Function
failcloseport:
CPS_ClosePort = False
End Function
______________________________________
'not using this function yet
Public Function CPS_Unload() As Boolean
Dim chan
On Error GoTo failunload
chan = DDEInitiate("CPSPLUS", "DRIVER")
DDEExecute chan, "[UNLOADCPS]" ' shutdown CPS Plus
DDETerminate chan
CPS_Unload = True
Exit Function
failunload:
CPS_Unload = False
End Function
sorry such a long portrayal of the problem.
any help would be appreciated.
Thank you
a.r.
Does any one out there have any experience with The CPS PLUS
serial data acquisition program?
I am trying to read data from a com port and put it into an Excel
spread sheet. I want to use CPS Plus because it is cheaper than the
WINWEDGE program and if i can get it to work i will be sending licensed
versions to several clients.
I am going in to an excel file cps.xls and into tools, then,macros,
then opening modules, and writing this code below into different
modules.
what i want to do is:::
i want to open the cps.xls file but i never want to write data to it,
as soon as i open it i want to open another file using this code:
Global Filename$
Sub AutoName()
' NameCheck Macro
Static StrLenDate, StrLenTime
SysDate$ = Date
SysTime$ = Time
StrLenDate = Len(SysDate$)
StrLenTime = Len(SysTime$)
A$ = "A"
P$ = "P"
Ms$ = "M"
Spaces$ = Chr(32)
Slashs$ = Chr(47)
Colons$ = Chr(58)
' Strip out slashes & spaces from the date
For j = 1 To StrLenDate
StringBit$ = Mid$(SysDate$, j, 1)
If StringBit$ = Spaces$ Then
StringBit$ = "-"
Mid$(SysDate$, j, 1) = StringBit$
End If
If StringBit$ = Slashs$ Then
StringBit$ = "-"
Mid$(SysDate$, j, 1) = StringBit$
End If
Next j
' Add a space to end of date
SysDate$ = SysDate$ + Spaces$
' strip out colon from the time
For k = 1 To StrLenTime
StringBit$ = Mid$(SysTime$, k, 1)
If StringBit$ = Colons$ Then
StringBit$ = " "
Mid$(SysTime$, k, 1) = StringBit$
End If
Next k
'Find the seconds portion of the time and strip it out
StringByte$ = Right(SysTime$, 5)
For M = 1 To 5
StringBit$ = Mid$(StringByte$, M, 1)
If StringBit$ = A$ Then
TimeofDay$ = A$ + Ms$ ' For AM
ElseIf StringBit$ = P$ Then
TimeofDay$ = TimeofDay$ + P$ + Ms$ 'For PM
Else
Mid$(StringByte$, M, 1) = ""
End If
Next M
'replace into time
TimeLen = Len(SysTime$)
SysTime$ = Mid$(SysTime$, 1, TimeLen - 5)
Filename$ = SysDate$ + SysTime$ + TimeofDay$
ActiveWorkbook.SaveAs (Filename$)
'This saves the file with the date and time as the filename
End Sub
Then i want to , write what ever data comes in from the comport to the
first row of this new file.
I got this to work. Now when the next line of data comes in , I need to
check the first 2 characters(this is the PartID#), if these 2
characters are the same as the last line of data's first 2 characters
then i just write this new line of data into the next row of the spread
sheet.But,if these first 2 characters are different than the the data
from the first line, then i need to open yet another file and put this
newdata in to it, that is where the problem starts, I am using
this code to do this:
Sub End_of_Log()
'
If PtID$ <> PrevPtID$ Then
ActiveWorkbook.Save
Workbooks.Open "C:\cps\cps.xls" 'cps
' Call auto_open
'activeworkbook.runautomacro xlautoopen
' call Autoname
ThisWorkbook.Close
End If
End Sub
*******
*******
When i do this it just opens up the cps.xls and it does not
get any new data
if i use the Call Auto_open or activeworkbook.runautomacro...,
it gives me the error
"the macro c:\cps.xls'|CPS_GetCOM1Data' cannot be found
if i call autoname i get different errors.
Does Any one know why or what to do??
********
*******
here is the rest of the Visual Basic code in the modules
' Functions:
' + LinkSerialPortToExcelSheet
' + BreakLinkSerialPortToExcelSheet
' + CPS_OpenPort
' + CPS_ClosePort
' + CPS_Unload
Type GenericLinkHolderType
RPointer(12) As Integer
SheetName(12) As String
Column(12) As Integer
End Type
Global PtID$,prevPtID$
Public GenericLinkHolder As GenericLinkHolderType
___________________________________
Sub Aut
pen() ' this sub runs automatically when you open thespreadsheet
LinkSerialPortToExcelSheet 1, "Sheet1", 1
' link from RS232 port COM1, to Sheet1 - column 1
End Sub
_________________________________
Sub Auto_Close() ' this macro runs automatically when you close the
spreadsheet
' Set up excel to stop reading data from CPS Plus / RS232 ports
BreakLinkSerialPortToExcelSheet 1, "Sheet1", 1
End Sub
__________________________________
Public Sub LinkSerialPortToExcelSheet(com_port_number As Integer,
Sheet_Name As String, Column_number As Integer)
On Error GoTo NoStartCPSPlus
If Not LinkSPortToExcelSheet(com_port_number, Sheet_Name,
Column_number) Then
End If
Exit Sub
NoStartCPSPlus:
MsgBox "DDE Connecion to CPS Plus application failed. Please start CPS
Plus manually and restart Excel."
End Sub
_________________________
Function LinkSPortToExcelSheet(com_port_number As Integer, Sheet_Name
As String, Column_number As Integer) As Boolean
On Error GoTo errhandler
Dim CallbackFunction As String
Dim CPS_DDELink As String
GenericLinkHolder.RPointer(com_port_number) = 1
GenericLinkHolder.SheetName(com_port_number) = Sheet_Name
GenericLinkHolder.Column(com_port_number) = Column_number
AppActivate Application.Caption ' Activate excel
Sheets(Sheet_Name).Activate
' activate sheet 1 and set up a DDE link to CPS Plus driver
CPS_DDELink = "CPSPLUS|DRIVER!COM" & CStr(com_port_number)
Sheets(Sheet_Name).Cells(Column_number, 50).Formula = "=" &
CPS_DDELink ' establish a DDE link
CallbackFunction = "CPS_GetCOM" & CStr(com_port_number) & "Data"
ThisWorkbook.SetLinkOnData CPS_DDELink, CallbackFunction
LinkSPortToExcelSheet = True
Exit Function
errhandler:
LinkSPortToExcelSheet = False
End Function
____________________________
Public Sub BreakLinkSerialPortToExcelSheet(com_port_number As Integer,
Sheet_Name As String, Column_number As Integer)
On Error Resume Next
Dim CPS_DDELink As String
AppActivate Application.Caption ' Activate excel
Sheets(Sheet_Name).Activate ' activate sheet 1
Sheets(Sheet_Name).Cells(Column_number, 50).Formula = "" ' remove the
dde link
CPS_DDELink = "CPSPLUS|DRIVER!COM" & CStr(com_port_number)
ThisWorkbook.SetLinkOnData CPS_DDELink, ""
End Sub
_________________________---
Private Sub CPS_GetCOM1Data()
' Type: Internal
' Reads data from serial RS232 port 1 - COM1.
' Use LinkSerialPortToExcelSheet to setup column and Sheet
On Error Resume Next
Dim prt_num As Integer
prt_num = 1 ' for COM1
Dim Com1Data As String
Dim rawnum As Integer
Dim F1 As Variant
Dim lbnd As Integer
Dim ubnd As Integer
chan = DDEInitiate("CPSPLUS", "COM1")
F1 = DDERequest(chan, "COM1DATA") ' get RS232 data from serial port
COM1 into Excel temp variable F1.
lbnd = LBound(F1)
ubnd = UBound(F1)
If lbnd = ubnd Then
Com1Data = CStr(F1(1))
' convert F1(1) - variant array to a string
If Len(Com1Data) > 0 Then
Call AutoName
Call End_of_log
PtID$ = Trim$(Mid$(Com1data, 2, 2)) ' start with position 2
Sheets(GenericLinkHolder.SheetName(prt_num)).Cells(GenericLinkHolder.RPointer(prt_num),
GenericLinkHolder.Column(prt_num)).Formula = Com1Data
GenericLinkHolder.RPointer(prt_num) =
GenericLinkHolder.RPointer(prt_num) + 1
End If
Else
For lbnd = 1 To ubnd
Com1Data = CStr(F1(lbnd))
' convert F1 - variant array to a string
If Len(Com1Data) > 0 Then
Call AutoName
Call End_of_log
PtID$ = Trim$(Mid$(Com1data, 2, 2)) ' start with position 2 because
position 1 is an asterisk
Sheets(GenericLinkHolder.SheetName(prt_num)).Cells
(GenericLinkHolder.RPointer(prt_num),
GenericLinkHolder.Column(prt_num)).Formula = Com1Data
GenericLinkHolder.RPointer(prt_num) =
GenericLinkHolder.RPointer(prt_num) + 1
End If
Next
End If
PrevPtID$ = PtID$
'Sheet1.Range("A65536").End(xlUp).Offset(1, 0).Value = Com1Data
DDETerminate chan
End Sub
--___________________________________________
____________________________________________
'not using this function yet
Public Function CPS_OpenPort(port_number As Integer) As Boolean
Dim chan
Dim ComPortName As String
On Error GoTo failopenport
ComPortName = "COM" + CStr(port_number)
chan = DDEInitiate("CPSPLUS", ComPortName)
DDEExecute chan, "[OPEN]"
DDETerminate chan
CPS_OpenPort = True
Exit Function
failopenport:
CPS_OpenPort = False
End Function
____________________________
'not using this function yet
Public Function CPS_ClosePort(port_number As Integer) As Boolean
Dim chan
Dim ComPortName As String
On Error GoTo failcloseport
ComPortName = "COM" + CStr(port_number)
chan = DDEInitiate("CPSPLUS", ComPortName)
DDEExecute chan, "[CLOSE]"
DDETerminate chan
CPS_ClosePort = True
Exit Function
failcloseport:
CPS_ClosePort = False
End Function
______________________________________
'not using this function yet
Public Function CPS_Unload() As Boolean
Dim chan
On Error GoTo failunload
chan = DDEInitiate("CPSPLUS", "DRIVER")
DDEExecute chan, "[UNLOADCPS]" ' shutdown CPS Plus
DDETerminate chan
CPS_Unload = True
Exit Function
failunload:
CPS_Unload = False
End Function
sorry such a long portrayal of the problem.
any help would be appreciated.
Thank you
a.r.