Using COM port with Excel VBA

[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

[SamT]

How many COM ports does your computer have?

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

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

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

[mage369]

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

[deanhuynh]

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)

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 !

[Leith Ross]

Hello deanhunyh,

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

[deanhuynh]

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 !

[s.dinah]

Hello Leith

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

[Leith Ross]

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.

[payanazo]

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

[Nienscecco]

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

[Sandunie]

I get this error when i try to run this. Please help me. Screenshot (2619).jpg

[Leith Ross]

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.

[Captain_Nemo]

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

[Leith Ross]

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?

[Captain_Nemo]

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

[Captain_Nemo]

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

[SOPNYA]

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.

[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!

[Joel1980]

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