Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 29

Thread: Using COM port with Excel VBA

  1. #1

    Using COM port with Excel VBA

    Hello,

    I have been looking on the net for way to interface a USB COM port with Excel 2013 with VBA.

    I have manage to find a solution that actually works.

    However there is a problem that I would with it that I cannot understand:

    the macro to list connected devices seems to be able to detect up to COM9.

    COM10 and above is not listed. Anyone have any ideas why?

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    How many COM ports does your computer have?

    Go to the Device manager and on the View menu, "Show Hidden Devices"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello s.dinah,

    The problem is with the API call CreateFile. This call is limited by the string convention used for identifying the COM port. If the COM names used are like COM1, COM2, etc. the highest COM port is COM9. Not sure why this is but I suspect it is a throwback to the early days of computing.

    Anyway, the good news is you can use a simple workaround by prefixing the COM port string with "\\.\" and the COM name like "\\.\COM1", "\\.\COM2", "\\.\COM10".

    The line of code you need to change is in the function CommOpen located in modCOMM module.

    Here is the updated line of code...
        ' Open serial port.
        udtPorts(intPortID).lngHandle = CreateFile("\\.\" & strPort, GENERIC_READ Or _
            GENERIC_WRITE, 0, ByVal 0&, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, 0)
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  4. #4
    Hello Leith

    Thank you for your input. The solution you provide worked brilliantly!

  5. #5
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello s.dinah,

    You're welcome. I haven't written any code for COM ports in about 15 years.

    Thanks for posting this code. It is very a comprehensive and flexible approach to programming the ports. Brilliant piece of work by the original author.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  6. #6
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    1
    Location

    Love you

    Quote Originally Posted by s.dinah View Post
    Hello,

    I have been looking on the net for way to interface a USB COM port with Excel 2013 with VBA.

    I have manage to find a solution that actually works.

    However there is a problem that I would with it that I cannot understand:

    the macro to list connected devices seems to be able to detect up to COM9.

    COM10 and above is not listed. Anyone have any ideas why?

    Thanks in advance
    Dude i totally love you, i have been looking over a week an example like this thanks from the bottom of my heart

  7. #7
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    1
    Location

    Hello

    Hello

    I'm new in the forum and on vba as well

    I use the code for my balance connected through serial port

    When i read i have some unreadable character

    I changed all parameters of the COM port but same unreadable character

    Any ideas

    Thank you

  8. #8

    UBER thanks

    I was struggling with this. Best example I could possibly find. Extremely precious! UBER Thanks to the original author. COM11 or higher is not an issue for me because I will always prefer to Fix My devices' COM ports and change COM number when needed. Again Bravo

  9. #9
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    1
    Location
    I get this error when i try to run this. Please help me. Screenshot (2619).jpg

  10. #10
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello Sandunie,

    The original workbook only uses cells A3:A4 (COM6 and Blank) for the COM Data Validation list. The attached workbook has COM1 to COM9 available in the drop down.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  11. #11

    Need Xon Xoff functionality

    Hi,

    At first thanks to s.dinah for sharing the quite helpful file. I had to modify it so it fits my needs (communication with a measurement device using different computers).
    Changes made are:
    • Userform added to select COM Port as needed
    • use of global variables to make calls from other userforms
    • separation of "read" from "connect", as I ran into difficulties with the global variables.


    Sample.xlsm

    But now I ran into issues I can't keep up with. The device I need to connect to uses the Xon/Xoff protocol and I have no clue.

    As far as my understanding goes the device is listening all the time providing Xon. While receiving and processing it turns Xoff.

    So how can I recognize if the device is in Xon or Xoff state so i can start an automated process using serveral commands depending on each other?

    Thanks for any hint or code snippets

  12. #12
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello Captain_Nemo,

    My first question is do you really need to the xon/xoff handshake with your device? Typically instruments uses this to prevent buffer overruns and is software controlled, unlike the RS-232 DTR and CTS signals which are hardware controlled.

    XON is ASCII code 17 and XOFF is ASCII code 19. This is a bidirectional signal. For example, suppose that the computer were sending data to an instrument which could not accept it because the buffer was full or was busy processing data. The instrument would send the XOFF character to the computer which would stop sending the data until it received an XON character to restart transmission. The same arrangements would apply for the reverse direction of data flow. It is important to know if the instrument is controlling the flow or the computer because the proper API parameters for the DCB structure must be set accordingly.

    What instrument are you communicating with?
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  13. #13
    Hello Leith,

    To be honest - On a scale of 1 to 10 on "protocol knowledge" I have a 1 ;-).

    I need to use several commands with different processing times. If I delay in general, the process will be longer than a manual process. Therefore I need to know when the instrument is ready to receive the next command.

    This is a part of the interface definition (roughly translated):
    The instrument is always "ready to receive" as it uses an interrupt control to process the interface data. After each CR received the device transmits XOFF. The command is being processed and after completion XON is send by the instrument.

    As for your explanation. Would it be sufficient to "send" a command and then loop the "read" until Chr(17) is send by the instrument?

    Thanks for your help
    Robert

  14. #14
    Hello Leith and others,

    I think i need some additional loop in my "read" sub to check whether the instrument is in XON or XOFF state otherwise my script runs amok. Something like

    do
    
    'Needed code snippet! readout of deviceComState 
    
    if deviceComState=XON then
    
    exit do
    else
    'nothing
    end if loop
    but I can't figure out how to retrieve the deviceComState.

    Maybe someone can help.

    Thanks Robert
    Last edited by Captain_Nemo; 06-18-2019 at 07:23 AM. Reason: formatting

  15. #15
    VBAX Newbie
    Joined
    Jun 2019
    Posts
    1
    Location

    Thanks

    Thanks
    But I can connect my COM port only. But I am unable to use it. I am sending quiry, it is giving same quiry back to me. Kindly rectify it.

  16. #16
    VBAX Newbie
    Joined
    Jul 2019
    Posts
    3
    Location
    Hello,

    I am trying to use this excel code to take torque readings from a calibration unit. I can receive the data but it all goes into the A15 cell. I would like to modify this program so that after each torque reading it moves down a cell so I can effectively separate each torque reading. How would I go about modifying this code in order to achieve this? Thanks for the help this forum is awesome!

  17. #17
    You wouldn't by any chance be trying to integrate an AKO calibration unit? I'm having similar problems.

  18. #18
    Quote Originally Posted by Ajust View Post
    Hello,

    I am trying to use this excel code to take torque readings from a calibration unit. I can receive the data but it all goes into the A15 cell. I would like to modify this program so that after each torque reading it moves down a cell so I can effectively separate each torque reading. How would I go about modifying this code in order to achieve this? Thanks for the help this forum is awesome!

    Hi, just looking at the code and thought I'd check if you managed to do what you were hoping to do. If you go to the Connect() function you can make the changes below to 1) Make it scroll to next row on each input and 2) Create a new string rather than the previous appending to old string behaviour.

    Public Sub Connect()
    Dim strSettings As String, strData As String
    Dim intPortID As Integer
    Dim lngStatus As Long
        
        strData = ""
        strSettings = ""
        Sheet1.Range("C15").Value = ""
        
        Dim rowout As Integer: rowout = 15                 <---------     Add a new Integer starting at 15 here, 15 is the row that it will appear in first
        
        
        With Sheet1
          
                strSettings = "baud=" & Left(.Range("D5").Value, Len(.Range("D5").Value) - 3) & " parity=" & Left(.Range("D7").Value, 1) & " data=" & .Range("D6").Value & " stop=" & .Range("D8").Value
               ' intPortID = Mid(.Range("D2").Value, 4, Len(.Range("D2").Value) - 3)       <-------  Ignore these changes, these are because the COM port dropdownt
                intPortID = 10                                                                                     <------- list won't populate on my machine so COM10 forced :D Bodge later
                lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), strSettings)
                
                If lngStatus = 0 Then
                    MsgBox "Connection to " & .Range("D2").Value & " Established!"
                    .Range("A64").Value = 1
                    Do While .Range("A64").Value = 1
                        lngStatus = CommRead(intPortID, strData, 1)
                        If lngStatus > 0 Then
                            .Range("C" & rowout).Value = strData                <------ this line will output the data to the current "C" & rowout instead of C15 
                            rowout = rowout + 1                                         <------ increments rowout after each line is received,
                        End If
                        'Sleep 500
                        DoEvents
                    Loop
                Else
                    lngStatus = CommGetError(strError)
                    MsgBox "Connection Failed. " & strError
                End If
           
        End With
        
    End Sub
    Quick bodge but it works for me on a 64-bit Windows 10 with Excel 2013

  19. #19
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    3
    Location
    Hello Leith
    I am trying to use your code to send command to serial port. however, when I send the command,
    I always get IngStatus = 0, that is <> Len(strData), and then get the message "can not send Data"
    How should I revise your code ?
    Thanks in advance

  20. #20
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello mc720519,

    There are many reasons this could be happening. I need to know more about the equipment you are communicating with.

    Are you the only person accessing the port?

    Which protocol or protocols does the equipment support, e.g. RTS, X/ON, etc?

    Which versions of Windows are you using?

    Is your computer 32 or 64 bits?
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

Tags for this Thread

Posting Permissions

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