COM Data to Excel

I have an Avery Weigh-Tronix 127 scale connected to computer with serial cable.  Scale is attached to COM port 1.  Pressing "Print" on the scale head sends weight recording from scale head to COM.  I would like to capture this data arriving at COM1 into an Excel spreadsheet.

Can someone assist with VB script or macro to allow capture of COM 1 data to Excel cell?  Possibly also check to see if data is already present in cell and move to next row, same column if so?
mrherndonAsked:
Who is Participating?
 
TheNauticanConnect With a Mentor Commented:
We dont have COM ports (so i couldn't try this ) but i found this you can try out.

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

-Naut
0
 
akbCommented:
I have done similar things in the past. What I did was write a program (I used Delphi) which continually monitored the serial (COM) port. When it received data it converted it to a suitable format and then injected it into the keyboard buffer.

Alternatively, the link provided by TheNautican takes you to a page which suggests the following VBA script:

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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.