Send data directly from COM3 to Excel

jaywind 

For 64-bit systems, you need to update the VBA declarations to use PtrSafe and adjust some data types to be compatible with 64-bit VBA. Here is an updated version of the code, which includes the necessary changes for a 64-bit system:

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 PtrSafe 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 LongPtr
Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As LongPtr, ByVal lpBuffer As String, ByVal nNumberOfBytesToRead As Long, ByRef lpNumberOfBytesRead As Long, ByVal lpOverlapped As Any) As Long
Declare PtrSafe Function WriteFile Lib "kernel32" (ByVal hFile As LongPtr, ByVal lpBuffer As String, ByVal nNumberOfBytesToWrite As Long, ByRef lpNumberOfBytesWritten As Long, ByVal lpOverlapped As Any) As Long
Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
Declare PtrSafe 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 LongPtr = -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

Dim hComm As LongPtr

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

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

Step 3: Add a Button to Run the Macro

1. Go back to Excel.
2. Go to Developer > Insert > Button (Form Control).
3. Draw the button on the worksheet.
4. 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

This VBA script, updated for 64-bit systems, allows you to read data from a serial port (COM3) and log it directly into an Excel worksheet. The provided code sets up serial communication using Windows API functions and logs incoming data to an Excel worksheet. Be sure to back up your file before testing the code to avoid data loss.

If that doesn't help you, I would recommend using third-party software.