Greetings...
So here is my issue I am trying to resolve. I want to capture data from my a serial port and place the data in different cells. I am using an Arduino in an application where I do a serial print, in that print I am sending two bits. Each one represents an input pulse to the Arduino telling an Excel macro to take a set of pictures with the web cams. Everything works as expected and I am currently using PLX-DAQ to read the pulses. I would like, however, to incorporate everything into one set of macros. So as I have investigated I have used another code that I found here on this forum and it works to a degree. I am able to read the data, however it puts all the data in one cell. It originally started in A1 and advanced down the A column until the timer timed out. The issue I am looking to resolve it to break up the data string, where in my Arduino code I send a serial print that sends a 1,1 that is seen in putty and the serial monitor of Arduino. What I would like excel to do is take the 1,1 and put it in two different cells. So I am looking for some advise on how to change the serial macro to make this happen...
Public Sub Receive_COM5_and_Save2()
Dim timeout As Date
Dim byte1 As Byte, chars As String, lineEnding As String
Dim COMport As Integer
Dim destCell As Range, rowOffset As Long
'Set starting cell where received data will be saved
With ThisWorkbook.ActiveSheet
Cells.Clear
Set destCell = Range("A1")
rowOffset = 0
End With
'Define line ending character(s)
'lineEnding = vbCr
lineEnding = vbCrLf
'Monitor COM port for 30 seconds
timeout = Now + TimeValue("00:00:10")
COMport = FreeFile
Close COMport
'Open COM5 port with baud rate 2400, No parity, 8 data bits, and 1 stop bit. These are the default port settings
'in HyperTerminal. In HyperTerminal, use Flow control = None.
Open "COM6:9600,N,8,1" For Random As #COMport Len = 1
Debug.Print Now; "Started"
chars = ""
While Now < timeout
'Get 1 byte
Get #COMport , , byte1
Debug.Print Now; IIf(Chr(byte1) < " ", "<" & byte1 & ">", Chr(byte1))
chars = chars & Chr(byte1)
If Right(chars, Len(lineEnding)) = lineEnding Then
Debug.Print "Line:" & Left(chars, Len(chars) - Len(lineEnding))
destCell.Offset(rowOffset, 0).Value = Left(chars, Len(chars) - Len(lineEnding))
rowOffset = rowOffset + 1
chars = ""
End If
DoEvents
Wend
Close #COMport
Debug.Print Now; "Finished"
'Save sheet as an .xlsx file
' destCell.Parent.Copy
' ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\COM_Port_Data.xlsx"
' ActiveWorkbook.Close savechanges:=False
End Sub
...in this code I am using COM6, not that, that matters...just thought I would include that bit of info...
Any assistance would be greatly appreciated...Thanks!
So here is my issue I am trying to resolve. I want to capture data from my a serial port and place the data in different cells. I am using an Arduino in an application where I do a serial print, in that print I am sending two bits. Each one represents an input pulse to the Arduino telling an Excel macro to take a set of pictures with the web cams. Everything works as expected and I am currently using PLX-DAQ to read the pulses. I would like, however, to incorporate everything into one set of macros. So as I have investigated I have used another code that I found here on this forum and it works to a degree. I am able to read the data, however it puts all the data in one cell. It originally started in A1 and advanced down the A column until the timer timed out. The issue I am looking to resolve it to break up the data string, where in my Arduino code I send a serial print that sends a 1,1 that is seen in putty and the serial monitor of Arduino. What I would like excel to do is take the 1,1 and put it in two different cells. So I am looking for some advise on how to change the serial macro to make this happen...
Public Sub Receive_COM5_and_Save2()
Dim timeout As Date
Dim byte1 As Byte, chars As String, lineEnding As String
Dim COMport As Integer
Dim destCell As Range, rowOffset As Long
'Set starting cell where received data will be saved
With ThisWorkbook.ActiveSheet
Cells.Clear
Set destCell = Range("A1")
rowOffset = 0
End With
'Define line ending character(s)
'lineEnding = vbCr
lineEnding = vbCrLf
'Monitor COM port for 30 seconds
timeout = Now + TimeValue("00:00:10")
COMport = FreeFile
Close COMport
'Open COM5 port with baud rate 2400, No parity, 8 data bits, and 1 stop bit. These are the default port settings
'in HyperTerminal. In HyperTerminal, use Flow control = None.
Open "COM6:9600,N,8,1" For Random As #COMport Len = 1
Debug.Print Now; "Started"
chars = ""
While Now < timeout
'Get 1 byte
Get #COMport , , byte1
Debug.Print Now; IIf(Chr(byte1) < " ", "<" & byte1 & ">", Chr(byte1))
chars = chars & Chr(byte1)
If Right(chars, Len(lineEnding)) = lineEnding Then
Debug.Print "Line:" & Left(chars, Len(chars) - Len(lineEnding))
destCell.Offset(rowOffset, 0).Value = Left(chars, Len(chars) - Len(lineEnding))
rowOffset = rowOffset + 1
chars = ""
End If
DoEvents
Wend
Close #COMport
Debug.Print Now; "Finished"
'Save sheet as an .xlsx file
' destCell.Parent.Copy
' ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\COM_Port_Data.xlsx"
' ActiveWorkbook.Close savechanges:=False
End Sub
...in this code I am using COM6, not that, that matters...just thought I would include that bit of info...
Any assistance would be greatly appreciated...Thanks!