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

How many COM ports does your computer have?

Go to the Device manager and on the View menu, "Show Hidden Devices"

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...

Code:

---

    ' Open serial port.
    udtPorts(intPortID).lngHandle = CreateFile("\\.\" & strPort, GENERIC_READ Or _
        GENERIC_WRITE, 0, ByVal 0&, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, 0)

---

Hello Leith

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

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.

Quote:

---

Originally Posted by s.dinah

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

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

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

I get this error when i try to run this. Please help me. Attachment 24185

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.

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.

Attachment 24393

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

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?

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

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

Code:

---

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

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.

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!

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

Quote:

---

Originally Posted by Ajust

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.

Code:

---

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

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

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?

Quote:

---

Originally Posted by Leith Ross

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?

---

Hi Leith,
I am using Win7-32bit, and use RS232 to control AP router by connecting USB port of Notebook. I think that is RTS/CTS
I can establish COM port and enter it. And, read data at [C15] (Receiver String), but just can not send command.
for your question: yes, I am the only person accessing the port.
BTW, I have a lose item at tool item: VideoSoft VSFlex 6.0 Control and VideoSoft VSFlex 7.0
Thanks

Quote:

---

Originally Posted by Leith Ross

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...

Code:

---

    ' Open serial port.
    udtPorts(intPortID).lngHandle = CreateFile("\\.\" & strPort, GENERIC_READ Or _
        GENERIC_WRITE, 0, ByVal 0&, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, 0)

---

---

Hi Leith! Thank you for the solution with COM >9. I am working on the updated file and with the modification of code as your suggestion. However, my COM is an USB-Serial (COM3) and I can't see it when Device List is pressed (Win10-64bits). Is there any trick to get around this that you might know of?

Thanks !

Hello deanhunyh,

Are you not seeing COM3 or the USB port Name or both?

Thanks for the quick reply Leith!

To clarify this is what i checked and done:
1. I checked in my Device Manager and I can see USB Serial Port (COM3) under Ports (COM & LPT) section and also I can see my USB Serial Converter under Universal Serial Bus Controllers. Which mean my converter is working i guess.
2. When I run the Excel Ver.1 (downloaded from this thread) and press the List Devices button, I couldn't see any COM listed and just return with MsgBox (Please select COM Port) - which I don't have anything to select.
3. I am connection my COM port to a PLC - Allen Bradley and I can verify that the connection is established and working by using RSLinx- the software from AB.

So, I can not see any COM3 in the list on excel.

Hope that you can spot some light on !

Hello deanhunyh,

I need to update some of the code to return meaningful error information. As is, there is very little and that is not helpful.

That would be much appreciated Leith!
Cheers !

Great job...i m not very expert in vba....if i must send a exadecimal value for example 00h 01h , what i must write?Thanks and sorry for the easy question..
'

Hi,
I have a few issues with this code. List function is not working, but when I fill the list manually, in my case COM2, I can establish connection to this port. Usually I can send data from freshly started sheet. However when I try to disconnect, connect again and send data I receive an error: "Could Not Send Data. Error (6): CommRead (ClearCommError)" Sometimes it is 2nd time I'm connecting, sometimes 4th.
Any idea?
Thanks

just thank to all of you, for the thread and to the original author of the thread (s.dinah), I will try to use the code to send SMS with GSM modem.