Solved

COM Data to Excel

Posted on 2014-02-19
2
1,028 Views
Last Modified: 2014-08-08
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?
0
Comment
Question by:mrherndon
2 Comments
 
LVL 8

Accepted Solution

by:
TheNautican earned 500 total points
ID: 39871376
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
 
LVL 13

Expert Comment

by:akb
ID: 39871515
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question