Using COM port with Excel VBA

[mc720519]

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

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

[Leith Ross]

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.

[deanhuynh]

That would be much appreciated Leith!
Cheers !

[tailele]

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

[joyopd]

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

[schiller]

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.