Receive data from COM port.

[Harry Geda]

Hello,

I have a temperature monitor that is connected to my computer
via RS232 to COM1.
(The actual connection is via a COM to USB cable.)

Small records of 10 charracters + Carriage-Return are sent per communication.

I can receive the data with Hyper Terminal program of XP on COM1.

If I could direct the data to EXCEL then I could resume with the data collection.

I can try to create a program to write the data in comma separated format in a file but that is not my intention.

Is this possible to do without programming?
What is the best way to do this?


Thank you,
Harry Geda

 

[John_w]

how can i get the received data stored in a xlsx file? should this already work with this code?

Here is the code modified to put the received data in Excel cells (the active sheet starting at A1) instead of outputting to the Immediate window (Debug.Print). The data is then saved in "COM_Port_Data.xlsx" in the same folder as the workbook containing the code.

'Receive data as separate bytes on COM5 port using VBA file I/O Open and Get statements
'and put the data in sheet cells and save the sheet as a .xlsx file

Public Sub Receive_COM5_and_Save()
    
    Dim timeout As Date
    Dim byte1 As Byte, chars 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
        Set destCell = .Range("A1")
        rowOffset = 0
    End With
    
    'Monitor COM port for 30 seconds
    
    timeout = Now + TimeValue("00:00:30")
    
    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 "COM5:2400,N,8,1" For Random As #COMport Len = 1
    
    Debug.Print Now; "Started"
    
    chars = ""
    While Now < timeout
    
        'Get 1 byte
        Get #COMport, , byte1
        
        If byte1 = vbCr Then
            'CR byte received, so write chars string to sheet cell
            destCell.Offset(rowOffset, 0).Value = chars
            rowOffset = rowOffset + 1
            chars = ""
        Else
            'Append byte as an ASCII character to chars string
            chars = chars & Chr(byte1)
        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

A received CR byte (ASCII 13) marks the end of a data 'record' and you will need to change the code to handle a different record delimiter byte or bytes.

 

[Toner]

Hi John

first of all, thanks a lot for the adapted code. i'm sure, this code would work fine if i would know how to handle the error i have
Let us see,what i understood correctly!

received CR byte (ASCII 13) marks the end of a data 'record' and you will need to change the code to handle a different record delimiter

Ok, thats clear cr = end of data record sign (could also be , or ; )

byte or bytes.

maybe its because of the language but at the moment i have no idea what this should mean.
i've implemented your code and set up the test software as described in my previous post. then i started random data send and received a "runtime error 13 type mismatch"
at this position

If byte1 = vbCr Then

so i thougt ok... he talks always about hyper terminal... lets try with hyper terminal -> download hyper terminal (i don't have/had it on my win). set up connection... so far so good.
start typing something -> same error. restart vba code... type a CR -> same error.

Microsoft help as google didn't help this time... something like 2hours later... i'm back here asking again for help... sorry


Best regards and thx in advance!

 

[John_w]

Ooops! I changed the If byte1 = vbCr Then line at the last minute without testing it, hence the type mismatch error. The line should be:

If byte1 = Asc(vbCr)

By "byte or bytes" I meant the record terminator (line ending) could be 1 byte (e.g. CR, as above) or 2 bytes (e.g. CR LF), or another byte or bytes specific to your data.

The code below has a better way of handling line endings - any number of characters - and is easier to modify to handle other line endings (record terminators). Note for HyperTerminal to send CR LF bytes instead of just CR you must check/change its settings: File -> Properties -> Settings tab -> ASCII Setup -> ASCII Sending - tick 'Send line ends with line feeds'.

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:30")
    
    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 "COM5:2400,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

 

[Toner]

Hy John

Thank you very much for this code and the explanations. i've already started to adapt it for my purposes but in comparison to your coding here my changes are just peanuts.
I also found a small issue, but i think i can handle it by myself.

its really friendly from you and the other volunteers that you take that much time to help lost souls in the infinite depth of vba coding.

For informative purposes to other readers:

Code works on Win 7 Ultimate and pro with office 2013 / 2016
the small issue is that if you stop the data transmission to your pc that runs this script, when script is still running... excel (2013/2016) mostly crashes.

my temporary workaround -> integrate a periodical autosave of the .xlsm file so if something happens... you almost don't loose your data.


Best regards

 

[WHB_01]

Hello John_W

First I would like to make compliments for this nice sample how to get serial data with VBA.
Now I was wondering if would start a new topic or continue with this one.
Although it’s about sending data to the comport I think it’s not misplaced in this topic, so here it comes…

I want to read data from a comport but I have to request for this data first.
In Hyper terminal I have to send a “2” to request for data.

How do I do this in excel VBA?

Thanks in advance!

Regards,
WHB_01

 

[John_w]

I want to read data from a comport but I have to request for this data first.
In Hyper terminal I have to send a “2” to request for data.

How do I do this in excel VBA?

The code for sending to a COM port is almost the same as for receiving, except you use Put instead of Get:

Public Sub Send_2()

    Dim COMport As Integer
    
    COMport = FreeFile
    Close #COMport
    
    'Open COM4 port with baud rate 2400, No parity, 8 data bits, and 1 stop bit
    
    Open "COM4:2400,N,8,1" For Random As #COMport
    
    'Either
    Put #COMport, , Asc("2")
    'Or
    'Put #COMport, , "2"
    
    Close #COMport

End Sub

There is more code for sending to a COM part in http://www.mrexcel.com/forum/excel-...odify-paste-send-cnc-machine.html#post3388683

 

[WHB_01]

Perfect!!

This will do the trick

Reg,
WHB_01

 

[anil_sur]

ok guys that was really gr8,

I just want to know how I can receive data continuously from my device

and can it be possible to create a file for one day , without stopping recording in excel ,

if say i am recording data for every 15 min for 24 hours after that it will create one file

pls help out this

Regards

 

[Lossy]

Hi everyone

i have a data logger that send throught a serial port a string in this format #000000x every 10 seconds
the string always start with # and finish with x
i need to read this number 000000 and put into a excel cell the first in A1, the second in A2, ...
how can i do ?

thanks very much

 

[John_w]

Hi everyone

i have a data logger that send throught a serial port a string in this format #000000x every 10 seconds
the string always start with # and finish with x
i need to read this number 000000 and put into a excel cell the first in A1, the second in A2, ...
how can i do ?

thanks very much

See if you can adapt the code earlier in this thread which reads fixed-length records (11 characters):

http://www.mrexcel.com/forum/excel-questions/488335-receive-data-com-port.html#post2410919

To extract the 000000 you could use the Mid function:

Mid(record,2,6)