Consulting

Results 1 to 10 of 10

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 Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    How many COM ports does your computer have?

    Go to the Device manager and on the View menu, "Show Hidden Devices"
    Please take the time to read the Forum FAQ

  3. #3
    VBAX Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    410
    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

  4. #4
    Hello Leith

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

  5. #5
    VBAX Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    410
    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

  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 Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    410
    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

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
  •