Solved

COM Data to Excel

Posted on 2014-02-19
2
1,146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
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…

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