+ Reply to Thread
Results 1 to 4 of 4

access rs232 port with vba

  1. #1

    access rs232 port with vba

    is there any way to send data to com port or listen to it and write data to
    file or sheet from vba in excell
    lp b

  2. #2
    Bob Phillips

    Re: access rs232 port with vba

    A quick trawl of Google came up with these posts http://tinyurl.com/4uzsy



    (remove nothere from the email address if mailing direct)

    "BoBri" <brilej@netsi.net> wrote in message
    > is there any way to send data to com port or listen to it and write data

    > file or sheet from vba in excell
    > lp b

  3. #3

    Re: access rs232 port with vba

    For this you can use "Microsoft Communications Control 6.0" which is
    To use it you must insert a UserForm in your code.
    You need not show the form to user, you just load it but don't show, but
    insert a UserForm.
    Change the name of this Userform to (say) ' frmComm '.
    When you insert the userform, normally the ToolsBox pops up. (If it
    doesn't then click on 'Veiw' Menu and select 'ToolsBox'.).

    Right click on ToolsBox and select 'Additional Controls'.
    In Additional Controls window scroll down and find "Microsoft
    Communications Control 6.0" and select it.
    In the file path that is displayed below verify that it points to
    MSCOMM32.OCX and click on OK.

    You will now find that a new control with picture of telephone has
    appeared on the ToolsBox.
    Now click on this control and drag it on the form. On the userform this
    picture can be anywhere, it doesn't matter. (This is picture is never
    shown even if you Show the userform.).

    On the userform (the name I assume is frmComm), select the drag MScomm
    picture and view its properties. Change its name to (say) ' RS232 '.

    Now that's all you have to do with the Userform.
    You can now refer to your RS232 connection in any module, or in class
    procedure (like Thisworkbook etc.) as 'frmComm.RS232' .

    In your procedure (in a module or any other class modules) when you want
    to communicate you start your code as under:

    Sub CommTest()
    Load frmComm 'Load the useform first.

    'Now set the communications port to one of COM1, COM2 etc.
    'For this you need to use only the number 1, 2 etc.
    'Assume it is COM1 then you set the port as under.

    frmComm.RS232.CommPort = 1

    'Before opening the port prepare for errors
    On Error Resume Next
    frmComm.RS232.PortOpen = True
    If Err = 8002 Then
    MsgBox "The COMPORT is not present or defective."
    Exit Sub
    End if
    If Err = 8004 Then
    Msgbox "The selected port is already open by other program."
    Exit Sub
    End if

    'Now you are ready to send data / read data.
    'For this the commands are .Output and .Input
    'But before proceeding some equipment when connected
    'my send a welcome string. So you first start checking
    'if it is sent and if sent you must clear the buffer.
    'Do as under
    Dim commInput
    If frmComm.RS232.InBufferCount > 0 Then
    frmComm.RS232.InputLen = 0
    commInput = frmComm.RS232.Input
    End If
    'The variable commInput now holds the received data.

    'Now you can send command to get data. Most commonly
    'the data is sent in ASCII. Check your communication
    'protocol, if you must send in ASCII. I assume it is
    'ASCII. Suppose the code to read a parameter is 5 and
    'terminating code is 10.
    'So you send the data as under.
    frmComm.RS232.Output = Chr(5) & Chr(10)

    'Now you must wait for the device to respond. This may take
    'few miliseconds to a couple of seconds. However you can't
    'wait indefinately. Max. wait time of 5 sec. should be more
    'than enough. We do this in a Doloop as under.

    Dim stTime As Long

    stTime = Timer

    Do While frmComm.RS232.InputBufferCount = 0
    If Timer > stTime + 5 Then Exit Do

    If frmComm.RS232.InputBufferCount = 0 Then
    MsgBox "Time out communicating with device." _
    & Chr(13) & "Ensure the device power is on and cable is connected."
    Exit Sub
    End If

    'Decide whether you want to read one character at a time
    'or all received data at a time.
    frmComm.RS232.InputLen = 1 'for 1 char. at a time
    'OR frmCommRS232.InputLen = 0 ' for all at once.

    commInput = frmComm.RS232.Input

    'Will read either one all all the characters depending
    'upon what you seleted. If you selected one at a time
    'you must process the input, and read next character
    'until all characters are read. You must do this before
    'you send the next .Output command. Other wise the 'leftover character
    will be still in the buffer and 'when you do .Input nextime that
    character will be sent first.

    'When done you can close the port with command:
    frmComm.RS232.PortOpen = False

    'And then unload the form when finally done
    Unload frmComm

    Hope this helps


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

  4. #4
    Thomas Lutz

    Re: access rs232 port with vba

    You can use the MSComm ActiveX control in Excel however it will
    require that you first have a copy of Visual Studio 6 installed in
    your system because the licensed version of MSComm ActiveX control
    only comes with Visual Studio 6.
    It will also require that you do quite a bit of VBA programming in
    Excel to make things work.
    Perhaps a better solution would be to use a third party tool that is
    designed for the job.
    An excellent tool that you can use to do serial communications
    directly from within Excel is called WinWedge and you can learn more
    about it on the following web site:

    On Sun, 2 Jan 2005 11:06:26 +0100, "BoBri" <brilej@netsi.net> wrote:

    >is there any way to send data to com port or listen to it and write data to
    >file or sheet from vba in excell
    >lp b

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1