none
Receive data from COM Port or USB live into Excel?

    Question

  • Hello,

    I have blocks of data that arrives from a remote terminal via RS232/COM1 port.
    Each block has carriage return at end of the data.

    I like to receive the data direcly into EXCEL so it will feel like the remote computer is typing into my EXCEL.
    Do I need an interface program outside EXCEL or a MACRO can do the same and how do I do it?

    Is there anyway to have the serial data go to keyboard buffer that EXCEL operates from it?

    Please advise,
    Harry

     

    Thursday, August 12, 2010 4:04 PM

Answers

  • Hi,

    Use NETComm.ocx.  Set the properties in the Load event, and open the port.  In the properties, set the CommPort, Settings, set RThreshold =1 , RTSEnable = 1, and PortOpen = True.

    In The OnComm event:

    Static Buffer As String

    Dim CRLFPos As Integer

    Buffer = Buffer & NETComm1.InputData 'or whatever name you use for the instance of NETComm

    CRLFPos = InStr(Buffer, vbCrLf)   'use vbCr, if only a carriage return is used, but I suspect vbCrLf is correct

    If CRLFPos > 0 Then

    Dim MyData As String

    MyData = Mid(Buffer, 1, CRLFPos-1)

    Buffer = Mid(Buffer, CRLFPos + 2)   'clean up Buffer, to remove data that are not needed, but to retain all that might make up the next message

    Process MyData   'you write this code

    End If

    That's all there is to it (except the nuts-and-bolts).

    Dick


    Dick Grier. Author of Visual Basic Programmer's Guide to Serial Communications 4. See www.hardandsoftware.net.
    • Marked as answer by Harry Geda Monday, August 16, 2010 7:33 PM
    Monday, August 16, 2010 3:37 PM

All replies

  • You can download NETComm.ocx from my homepage.  It works fine in Excel VBA.  Install it, then from the Toolbox, drop an instance on either a Worksheet or Form in you Excel application.

    I have several examples on the CDROM that accompanies my book that illustrate this.

    Dick


    Dick Grier. Author of Visual Basic Programmer's Guide to Serial Communications 4. See www.hardandsoftware.net.
    Friday, August 13, 2010 4:56 PM
  • However...  On further reading of your question... I suppose what you really want to do is to change data in the Worksheet?  If so, then you would not try to do anthing with the Keyboard, but would simply change Worksheet cell content by parsing receive data and making the appropriate changes.  Again, I have simple examples for this, but the actual details will depend on things that I don't know about how you want you program to work.
    Dick Grier. Author of Visual Basic Programmer's Guide to Serial Communications 4. See www.hardandsoftware.net.
    Friday, August 13, 2010 4:59 PM
  • Hello Dick,

    I have a remote temperature monitor that is connected to my computer
    via RS232 to COM1.

    Small records of 10-15 charracters are sent per communication.
    They arrive to COM1 of my computer. They terminate with a carriage-return.

    I can receive the data with Hyper Terminal program of XP.

    If I could direct the data to EXCEL then I could resume with the data collection.

    I can create a program to write the data in EXCEL format in a file but
    that is not my intention.

    There is no editing since the device is just an automated hardware.

    Thank you,
    Harry

     

     

    Saturday, August 14, 2010 1:20 PM
  • Hi,

    Use NETComm.ocx.  Set the properties in the Load event, and open the port.  In the properties, set the CommPort, Settings, set RThreshold =1 , RTSEnable = 1, and PortOpen = True.

    In The OnComm event:

    Static Buffer As String

    Dim CRLFPos As Integer

    Buffer = Buffer & NETComm1.InputData 'or whatever name you use for the instance of NETComm

    CRLFPos = InStr(Buffer, vbCrLf)   'use vbCr, if only a carriage return is used, but I suspect vbCrLf is correct

    If CRLFPos > 0 Then

    Dim MyData As String

    MyData = Mid(Buffer, 1, CRLFPos-1)

    Buffer = Mid(Buffer, CRLFPos + 2)   'clean up Buffer, to remove data that are not needed, but to retain all that might make up the next message

    Process MyData   'you write this code

    End If

    That's all there is to it (except the nuts-and-bolts).

    Dick


    Dick Grier. Author of Visual Basic Programmer's Guide to Serial Communications 4. See www.hardandsoftware.net.
    • Marked as answer by Harry Geda Monday, August 16, 2010 7:33 PM
    Monday, August 16, 2010 3:37 PM
  • Hello Dick,

    This is very complicated for me.

    I have no idea where are you doing all of this changes.

    Forgive me but if the solution is only in a download from your page then I am not doing  it.

    Regards,

    Harry

    Monday, August 16, 2010 7:33 PM
  • Dick,

    I am marking this as answer but I have not tried it.

    Thank you,

    Harry

    Monday, August 16, 2010 8:26 PM
  • You can contact me via email.  I can reply with a simple example.

    Dick


    Dick Grier. Author of Visual Basic Programmer's Guide to Serial Communications 4. See www.hardandsoftware.net.
    Tuesday, August 17, 2010 2:34 PM