+ Reply to Thread
Results 1 to 8 of 8

Tutorial - Use MSCOMM with a serial device

  1. #1
    Registered User
    Join Date
    12-01-2005
    Posts
    45

    Tutorial - Use MSCOMM with a serial device

    Hi all,

    This forum has been a great help to me in the last few months and I wanted to give something back. Although I am no Excel or VBA expert...I have learnt one heck of a lot in recent times.

    I have had some major issues using MSCOMM and finding information/support on the web. So I thought I would share how I got it working on an excel worksheet to communicate with a serial device.

    Thanks

    Rob

    ----------------------------------------------------------------

    First off, if you hav'nt already done so - you will need to obtain the MSCOMM32.OCX Active X library.

    Start here - http://www.yes-tele.com/mscomm.html

    You will need to register it. Go to command prompt and type the following -

    regsvr32 C:\Windows\System\MSCOMM32.OCX

    You should get a message to indicate that the control has been registered.

    Then you may need to update the registry with this key -

    [HKEY_CLASSES_ROOT\Licenses\4250E830-6AC2-11cf-8ADB-00AA00C00905]
    @ = "kjljvjjjoquqmjjjvpqqkqmqykypoqjquoun"

    As far as the registry stuff goes, I am no expert. This worked for me...and as far as I know it is legal. But if you have any concerns...or if you are not familiar with working within the registry...I would suggest doing some more research first!

    Now....you should be ready to program with MSCOMM!!!

    Open up a new Excel workbook. Make sure you can see the 'Control Toolbox (View>>>Toolbars>>>Control Toolbox).

    Rename your worksheet 'SerialPort'.

    Once you can see the toolbox- there should be a little toolbox icon. Click the icon and select 'Microsoft Communication Control' from the list.

    The cursor will change, allowing you to draw a box on the worksheet. This is the control. It does not matter where you place it - as when you open the workbook this will not be visible to the user.

    Once you have created the control, right-click and select 'View Code'.

    You should see something like -

    Private Sub MSComm1_OnComm()

    End Sub

    This is the 'OnComm' event and tells excel what to do when data is received from the serial port. We will come back to this later. Lets write some code to open a port up....

    Insert the following into a new sub under the OnComm sub -

    Sub OpenPort()

    'Open the COM Port with the relevant settings

    Worksheets("SerialPort").MSComm1.CommPort = 1
    Worksheets("SerialPort").MSComm1.Settings = "9600,n,8,1"
    Worksheets("SerialPort").MSComm1.RThreshold = 1
    Worksheets("SerialPort").MSComm1.InBufferSize = 4096
    Worksheets("SerialPort").MSComm1.PortOpen = True


    End Sub

    The sub above will configure the port when you try and open it. 'CommPort' is the port number your device is connected to. 'Settings' are the device setting (baud rate, parity, etc) and are usually in the device documentation (you can also use the device with Hyperterminal to get these settings).

    PortOpen = True tells Excel to open the port with the above settings.

    RThreshold is what we are interested in here. By setting it to '1' we are telling Excel to fire the 'OnComm' code whenever data is received from the serial port.

    The way I call the port open sub, is to have a worksheet onchange event. I only want the port to open when the user selects a cell in a particular column range, so I am using the following -

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Columns("A:A")) Is Nothing Then

    If Target.Value = "" Then

    Call OpenPort

    End If
    End If

    Application.EnableEvents = True

    End Sub

    This piece of code must sit against the 'Worksheet' 'Selection Change' section. Use the dropdown boxes at the top of your VBA editor to select this.

    So now, if a cell in column 'A' is selected, the port will be opened with my settings. As RThreshold is set to '1' - the OnComm code will be called whenever I try to read from the cheque reader and am in the 'A' column.

    Now to tell it do something when data is received....

    Go back to your OnComm make it look like -

    Private Sub MSComm1_OnComm()

    If Worksheets("SerialPort").MSComm1.CommEvent = comEvReceive Then
    Call GetData
    End If

    End Sub

    The CommEvent - comEvReceive tells Excel that if data is coming in from the serial device....do something. In this case, Call sub "GetData".

    Now, to create sub "GetData" that will grab the information from the device and place it in your worksheet.

    The sub below is very straightforward - but will help get you started!

    Private Sub GetData()

    Dim MyData As String

    Wokrsheets("SerialPort").MSComm1.InputLen = 0

    MyData = Worksheets("SerialPort").MSComm1.Input
    ActiveCell.Value = MyData

    MyData = ""

    Worksheets("SerialPort").MSComm1.PortOpen = False

    End Sub

    The 'InputLen' setting tells Excel how much data to read from the serial device. Setting it to zero tells it to keep reading until the end of the file (ie it gets all of the data). If you know your string is always going to be 10 digits...you could set this to 10. Or you may need a function to pull the characters one at a time until you get to a specific value. It all depends on the device and what you are trying to achieve....

    And you are done! You should now have a very basic working script to read data from a serial device and place it into your worksheet.

    I hope this helps someone....my apologies for any poor terminology/lack of clarity and any mistakes I have made. I am not using the same code myself...and there is a great deal more that you will need to learn to use MSComm effectively. It is also the first time I have put something together like this (but that's obvious :P)

    Hopefully this is a start for anyone else who is struggling to get going with this.

    Have fun!

    Rob

  2. #2
    JimBob
    Guest

    RE: Tutorial - Use MSCOMM with a serial device

    Wow this is good ... will it work for data coming through USP port?

    Thanks,
    Jim Anderson

    "systemx" wrote:

    >
    > Hi all,
    >
    > This forum has been a great help to me in the last few months and I
    > wanted to give something back. Although I am no Excel or VBA expert...I
    > have learnt one heck of a lot in recent times.
    >
    > I have had some major issues using MSCOMM and finding
    > information/support on the web. So I thought I would share how I got it
    > working on an excel worksheet to communicate with a serial device.
    >
    > Thanks
    >
    > Rob
    >
    > ----------------------------------------------------------------
    >
    > First off, if you hav'nt already done so - you will need to obtain the
    > MSCOMM32.OCX Active X library.
    >
    > Start here - http://www.yes-tele.com/mscomm.html
    >
    > You will need to register it. Go to command prompt and type the
    > following -
    >
    > regsvr32 C:\Windows\System\MSCOMM32.OCX
    >
    > You should get a message to indicate that the control has been
    > registered.
    >
    > Then you may need to update the registry with this key -
    >
    > [HKEY_CLASSES_ROOT\Licenses\4250E830-6AC2-11cf-8ADB-00AA00C00905]
    > @ = "kjljvjjjoquqmjjjvpqqkqmqykypoqjquoun"
    >
    > As far as the registry stuff goes, I am no expert. This worked for
    > me...and as far as I know it is legal. But if you have any
    > concerns...or if you are not familiar with working within the
    > registry...I would suggest doing some more research first!
    >
    > Now....you should be ready to program with MSCOMM!!!
    >
    > Open up a new Excel workbook. Make sure you can see the 'Control
    > Toolbox (View>>>Toolbars>>>Control Toolbox).
    >
    > Rename your worksheet 'SerialPort'.
    >
    > Once you can see the toolbox- there should be a little toolbox icon.
    > Click the icon and select 'Microsoft Communication Control' from the
    > list.
    >
    > The cursor will change, allowing you to draw a box on the worksheet.
    > This is the control. It does not matter where you place it - as when
    > you open the workbook this will not be visible to the user.
    >
    > Once you have created the control, right-click and select 'View Code'.
    >
    > You should see something like -
    >
    > Private Sub MSComm1_OnComm()
    >
    > End Sub
    >
    > This is the 'OnComm' event and tells excel what to do when data is
    > received from the serial port. We will come back to this later. Lets
    > write some code to open a port up....
    >
    > Insert the following into a new sub under the OnComm sub -
    >
    > Sub OpenPort()
    >
    > 'Open the COM Port with the relevant settings
    >
    > Worksheets("SerialPort").MSComm1.CommPort = 1
    > Worksheets("SerialPort").MSComm1.Settings = "9600,n,8,1"
    > Worksheets("SerialPort").MSComm1.RThreshold = 1
    > Worksheets("SerialPort").MSComm1.InBufferSize = 4096
    > Worksheets("SerialPort").MSComm1.PortOpen = True
    >
    >
    > End Sub
    >
    > The sub above will configure the port when you try and open it.
    > 'CommPort' is the port number your device is connected to. 'Settings'
    > are the device setting (baud rate, parity, etc) and are usually in the
    > device documentation (you can also use the device with Hyperterminal to
    > get these settings).
    >
    > PortOpen = True tells Excel to open the port with the above settings.
    >
    > RThreshold is what we are interested in here. By setting it to '1' we
    > are telling Excel to fire the 'OnComm' code whenever data is received
    > from the serial port.
    >
    > The way I call the port open sub, is to have a worksheet onchange
    > event. I only want the port to open when the user selects a cell in a
    > particular column range, so I am using the following -
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    >
    > If Not Intersect(Target, Columns("A:A")) Is Nothing Then
    >
    > If Target.Value = "" Then
    >
    > Call OpenPort
    >
    > End If
    > End If
    >
    > Application.EnableEvents = True
    >
    > End Sub
    >
    > This piece of code must sit against the 'Worksheet' 'Selection Change'
    > section. Use the dropdown boxes at the top of your VBA editor to select
    > this.
    >
    > So now, if a cell in column 'A' is selected, the port will be opened
    > with my settings. As RThreshold is set to '1' - the OnComm code will be
    > called whenever I try to read from the cheque reader and am in the 'A'
    > column.
    >
    > Now to tell it do something when data is received....
    >
    > Go back to your OnComm make it look like -
    >
    > Private Sub MSComm1_OnComm()
    >
    > If Worksheets("SerialPort").MSComm1.CommEvent = comEvReceive Then
    > Call GetData
    > End If
    >
    > End Sub
    >
    > The CommEvent - comEvReceive tells Excel that if data is coming in from
    > the serial device....do something. In this case, Call sub "GetData".
    >
    > Now, to create sub "GetData" that will grab the information from the
    > device and place it in your worksheet.
    >
    > The sub below is very straightforward - but will help get you started!
    >
    > Private Sub GetData()
    >
    > Dim MyData As String
    >
    > Wokrsheets("SerialPort").MSComm1.InputLen = 0
    >
    > MyData = Worksheets("SerialPort").MSComm1.Input
    > ActiveCell.Value = MyData
    >
    > MyData = ""
    >
    > Worksheets("SerialPort").MSComm1.PortOpen = False
    >
    > End Sub
    >
    > The 'InputLen' setting tells Excel how much data to read from the
    > serial device. Setting it to zero tells it to keep reading until the
    > end of the file (ie it gets all of the data). If you know your string
    > is always going to be 10 digits...you could set this to 10. Or you may
    > need a function to pull the characters one at a time until you get to a
    > specific value. It all depends on the device and what you are trying to
    > achieve....
    >
    > And you are done! You should now have a very basic working script to
    > read data from a serial device and place it into your worksheet.
    >
    > I hope this helps someone....my apologies for any poor terminology/lack
    > of clarity and any mistakes I have made. I am not using the same code
    > myself...and there is a great deal more that you will need to learn to
    > use MSComm effectively. It is also the first time I have put something
    > together like this (but that's obvious :P)
    >
    > Hopefully this is a start for anyone else who is struggling to get
    > going with this.
    >
    > Have fun!
    >
    > Rob
    >
    >
    > --
    > systemx
    > ------------------------------------------------------------------------
    > systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
    > View this thread: http://www.excelforum.com/showthread...hreadid=539481
    >
    >


  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    tunisia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Tutorial - Use MSCOMM with a serial device

    Hi,

    First thank you for all these detailed information.
    But i still have a problem: i installed MSCOMM32.OCX and registred it successefully (using cmd comands as it was shown above) and restarted my PC, but When i try to draw a box on the worksheet ('Microsoft Communication Control' ) this message is shown:Impossible to insert an object !
    Could someone help me resolve this problem?
    Thank you so much

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Tutorial - Use MSCOMM with a serial device

    douma,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  5. #5
    Registered User
    Join Date
    04-22-2013
    Location
    tunisia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Tutorial - Use MSCOMM with a serial device

    OK done

  6. #6
    Registered User
    Join Date
    10-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Tutorial - Use MSCOMM with a serial device

    I just wanted to add my thanks for this little heads-up, and especially for the registry key which actually worked for me and got rid of the invalid license message. Why on earth do M$ need to make life so difficult for us.

  7. #7
    Registered User
    Join Date
    12-04-2016
    Location
    Turkey
    MS-Off Ver
    2013
    Posts
    1

    runtime error "438" object doesn't support this property or method

    Hi systemx,

    I try using your code. A button runs this sub below.

    Sub OpenPort()

    'Open the COM Port with the relevant settings

    Worksheets("SerialPort").MSComm1.CommPort = 1
    Worksheets("SerialPort").MSComm1.Settings = "9600,n,8,1"
    Worksheets("SerialPort").MSComm1.RThreshold = 1
    Worksheets("SerialPort").MSComm1.InBufferSize = 4096
    Worksheets("SerialPort").MSComm1.PortOpen = True


    End Sub

    But when it comes to the Worksheets("SerialPort").MSComm1.CommPort = 1 line, I have this 438 runtime error. I think I have a problem
    with the mscomm32.ocx file. But I added and registered this file into the syswow64 folder.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,790

    Re: Tutorial - Use MSCOMM with a serial device

    emrahyigit,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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