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]

You could use VBA file I/O to read data from the COM port into Excel. The following example polls the COM5 port every 200 milliseconds for 30 seconds and displays the data received in the Immediate window. To make it work with COM1, just change the Open statement.

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Receive_COM5()
    
    Dim COM5file As Integer
    Dim timeout As Date
    Dim record As String * 11, emptyRecord As String * 11
    Dim recLen As Integer
    Dim inputByte As Integer
    
    recLen = Len(record)
        
    'Open COM5 port with baud rate 2400, No parity, 8 data bits and 1 stop bit
    
    COM5file = FreeFile
    Open "COM5:2400,N,8,1" For Random As #COM5file Len = recLen
    
    'Monitor port for 30 seconds
    
    timeout = Now + TimeValue("00:00:30")
    
    Debug.Print "Started"
    
    While Now < timeout
        Get #COM5file, , record
        
        If record <> emptyRecord Then
        
            Debug.Print Now; "<" & record & ">"
            
            'Display each byte
            
            For i = 1 To recLen
                inputByte = Asc(Mid(record, i, 1))
                If inputByte = 0 Then
                    'No character in this position
                ElseIf inputByte >= 32 And inputByte <= 126 Then
                    'Printable character
                    Debug.Print "<" & inputByte & "> "; Chr(inputByte)
                Else
                    'Non-printable character
                    Debug.Print "<" & inputByte & ">"
                End If
            Next
        End If
        
        DoEvents
        Sleep 200
    Wend
    
    Close #COM5file
    Debug.Print "Finished"

End Sub

I don't have any serial devices or a null-modem connected to the COM ports on my computer, so to test the above code, I installed the com0com null-modem emulator and created a virtual port pair between COM2 and COM5. I then opened COM2 in HyperTerminal and characters typed there were captured by the running program.

If you prefer an event-driven approach you would need to program the MSComm control - http://www.yes-tele.com/mscomm.html.

 

[Harry Geda]

John,

Thank you for the post.
I will try it later today.

Regards,
Harry

 

[felix88]

Hi,
I have an error(run time error '53': File not found) with the code above after I tried to run the code in VBA Excel.
However, after I comment the two lines below, then it would be able to run.

Open "COM5:2400,N,8,1" For Random As #COM5file Len = recLen
Get #COM5file, , record


Could you point me my error or what could be the possible settings I might have done it wrongly.


Thanks.

Best regards,
Felix

 

[John_w]

felix88, which COM port are you using? As stated in my post, the code above is for COM port 5. If you're using COM port 2 then the code would change to:

    Open "COM2:2400,N,8,1" For Random As #COM5file Len = recLen

and maybe change the variable name COM5file to reflect the different port number.

PS - please use [ CODE]VBA code here[ /CODE] tags (without the spaces) when posting code.

 

[felix88]

John,
Thanks for your reply.
Yes, I tried with port1 and I changed the above mentioned code to port1.
However I got the compile error 75: file/path not found

I run the code on excel 2007/ windows 7 use the code above exactly the same.

Did I miss any setting or code that is needed?


Thanks.


Regards,
Yong Han

 

[John_w]

That code is all you need to answer the specific question asked by the OP, but your situation might be different.

I tested the code again with the com0com Null-modem emulator as described and it still works for me. I'm using Windows XP and Excel 2003. I don't know if it works on Windows 7 and Excel 2007.

If you have a serial device connected to COM1 then I suggest you first test the comms using HyperTerminal (or the equivalent on Windows 7), without involving VBA.

 

[Michael M]

Post #8 reported

 

[Legacy 255223]

I have tried using the above VBA code to read from a serial port and it works very well but with one small issue: If there is no incoming data, or less data than RecLen, then the 'Get' statement locks up until there is more data available. I have also tried changing to 'binary' instead of 'random' for the filemode so I can read one byte at a time but still have the same problem. Is there some way of checking if there is incoming data available before calling 'get' (eg like the 'kbhit' function in C) or alternatively some way of making the 'get' function time out if it doesn't return within a certain time?

 

[ingvarlinden]

That code is all you need to answer the specific question asked by the OP, but your situation might be different.

Hello. I must thank for this info and code - it has get me close to my solution). Excaktly as you i have excel2003 and runs this in makro.

I hope to catch info from an attached GPS in COM18 sending NMEAcodes... BUT do you have any idea why the code above just finds blank/empty records? So the code doesnt comes in to the central parts.

I have tested to take out my GPS and it works just the same way! Also when i change the code to a comport that doesn exist it behavse the same!
Why doesnt i hook on the signals at all?? Do you have any idea och thoughts it would be lovely!

The gps is a bu353 and have a programproduct sent along with it which is working fine and shows the COM18 code in a window rolling over ghe screen. So there is a signal in the port!
Im stuck.....

Ingvar Sweden