Excel connect to a COM port

Connecting to a COM port gives your Excel project functionality for external devices such as Barcode readers, key button readers such as point of sale login widgets, temperature sensors etc. A straightforward example script to manage the communications between the device and Excel is provided below, and as an example of what devices can be used, see the picture here.

iButton_Reader_for_Universal_Serial_Port_DS9097U-009_by_Dallas_Maxim_Product-300x300

Of course, newer PC’s, Laptops and Tablets don’t necessarily have a COM port (RS232) and will typically manage communications through a USB connector. Where the items require an RS232 Serial COM port, this example code is invaluable.

If you’re looking for a PC Card to attach the COM RS232 port to your desktop PC, we recommend Maplin store, or via eBay. The PC boards aren’t expensive – £15 – £20 only or you could try your local PC Exchange store.

If you need any assistance with this code example, or implementing this kind of project with your systems, feel free to leave a message or contact us via the form. We’d be more than happy to help get your project up and running.



Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Receive_COM5()

Dim COM5file As Integer
Dim timeout As Date
Dim record As String * 11, emptyRecord As String * 11
Dim recLen As Integer
Dim inputByte As Integer

recLen = Len(record)

'Open COM5 port with baud rate 2400, No parity, 8 data bits and 1 stop bit

COM5file = FreeFile
Open "COM5:2400,N,8,1" For Random As #COM5file Len = recLen

'Monitor port for 30 seconds

timeout = Now + TimeValue("00:00:30")

Debug.Print "Started"

While Now < timeout Get #COM5file, , record If record <> emptyRecord Then

Debug.Print Now; "<" & record & ">"

'Display each byte

For i = 1 To recLen
inputByte = Asc(Mid(record, i, 1))
If inputByte = 0 Then
'No character in this position
ElseIf inputByte >= 32 And inputByte <= 126 Then 'Printable character Debug.Print "<" & inputByte & "> "; Chr(inputByte)
Else
'Non-printable character
Debug.Print "<" & inputByte & ">"
End If
Next
End If

DoEvents
Sleep 200
Wend

Close #COM5file
Debug.Print "Finished"

End Sub


Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *