Solved

COM Data to Excel

Posted on 2014-02-19
2
929 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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now