Link to home
Start Free TrialLog in
Avatar of mrherndon
mrherndon

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of TheNautican
TheNautican

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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