Send data directly from COM3 to Excel

jaywind 

For a simpler solution to send data from a device connected via a serial port (COM3) directly to Excel, you can use third-party software that bridges the serial port data to Excel. One such tool is Tera Term, a free terminal emulator that can log serial data to a file, combined with Excel's external data import feature. A other third-party software is PuTTY for Serial Communication.

Another approach would be using Python and extensive settings. Python allows you to fully customize the data collection and processing pipeline to fit your exact needs. You can control how data is read, parsed, processed, and written to Excel. But it requires programming knowledge and is more time-consuming to create than using third-party software.

An additional option would be VBA. VBA can handle communication with the serial port by leveraging Windows API functions.

Step-by-Step Guide Using VBA

Step 1: Set Up VBA in Excel

1. Open Excel:
• Open a new or existing workbook where you want to log the data.
2. Open the VBA Editor:
• Press Alt + F11 to open the VBA editor.
3. Insert a New Module:
• In the VBA editor, go to Insert > Module to create a new module.

Step 2: Add VBA Code for Serial Communication

1. Define API Functions:

• Add the following code to the new module to define the necessary Windows API functions:

Vba Code is untested backup your file

Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Declare Function ReadFile Lib "kernel32" (ByVal hFile As Long, ByVal lpBuffer As String, ByVal nNumberOfBytesToRead As Long, ByRef lpNumberOfBytesRead As Long, ByVal lpOverlapped As Any) As Long
Declare Function WriteFile Lib "kernel32" (ByVal hFile As Long, ByVal lpBuffer As String, ByVal nNumberOfBytesToWrite As Long, ByRef lpNumberOfBytesWritten As Long, ByVal lpOverlapped As Any) As Long
Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Declare Function GetLastError Lib "kernel32" () As Long

Const GENERIC_READ As Long = &H80000000
Const GENERIC_WRITE As Long = &H40000000
Const OPEN_EXISTING As Long = 3
Const FILE_ATTRIBUTE_NORMAL As Long = &H80
Const INVALID_HANDLE_VALUE As Long = -1

 2. Create Functions to Open, Read from, and Close the Serial Port:

• Add the following functions to handle the serial port communication:

Vba Code is untested backup your file

Dim hComm As Long

Function OpenSerialPort(portName As String) As Boolean
    hComm = CreateFile(portName, GENERIC_READ Or GENERIC_WRITE, 0, 0, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, 0)
    If hComm = INVALID_HANDLE_VALUE Then
        OpenSerialPort = False
    Else
        OpenSerialPort = True
    End If
End Function

Function ReadSerialPort() As String
    Dim buffer As String * 1024
    Dim bytesRead As Long
    Dim result As Long
    result = ReadFile(hComm, buffer, Len(buffer), bytesRead, ByVal 0&)
    If result <> 0 Then
        ReadSerialPort = Left(buffer, bytesRead)
    Else
        ReadSerialPort = ""
    End If
End Function

Function CloseSerialPort() As Boolean
    CloseHandle hComm
    CloseSerialPort = True
End Function

3. Write the Main Subroutine to Read Data and Log to Excel:

• Add a subroutine to open the serial port, read data, and write it to the worksheet:

Vba Code is untested backup your file

Sub ReadFromSerialPort()
    Dim data As String
    Dim row As Long
    
    ' Open the serial port
    If Not OpenSerialPort("COM3") Then
        MsgBox "Failed to open COM3"
        Exit Sub
    End If
    
    ' Set the initial row for logging data
    row = 2 ' Assuming row 1 is for headers
    
    ' Loop to continuously read data
    Do
        data = ReadSerialPort()
        If data <> "" Then
            ' Write data to the worksheet
            ThisWorkbook.Sheets(1).Cells(row, 1).Value = Now
            ThisWorkbook.Sheets(1).Cells(row, 2).Value = data
            row = row + 1
        End If
        
        ' Check for user interrupt (e.g., pressing Esc)
        DoEvents
    Loop Until False
    
    ' Close the serial port
    CloseSerialPort
End Sub

 4. Add a Button to Run the Macro:

• Go back to Excel.
• Go to Developer > Insert > Button (Form Control).
• Draw the button on the worksheet.
• Assign the ReadFromSerialPort macro to the button.

Usage

1. Click the Button:
• Click the button you added to start reading data from the serial port.
2. Data Logging:
• The macro will continuously read data from the serial port and log it to the worksheet, along with a timestamp.

Summary

Using VBA for this task allows you to stay within the Excel environment and avoid the need for third-party software or additional programming languages. The provided code sets up serial communication using Windows API functions and logs incoming data to an Excel worksheet. Adjust the COM port and baud rate settings as needed for your specific device.

The VBA Code edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.