In a previous thread I was complaining about how MS dropped the MSComm API for office 07 and 10. After a lot of searching I finally found a way to communicate through the USB port using Excel and thought I would share. Below is the code I used and I also attached the Excel file and the source code I used to communicate with a PIC32 using a FTDI interface chip. The Excel file just opens, writes, and reads data over a COM port. The PIC32 just echo's data that was received using the UART interrupt and toggles an LED to show UART activity. The VBA code came from here: I just modified the code slightly for my own purposes.
Due to the character restrictions, I will post everything in a few posts. Code: Option Explicit '- ' ' This VB module is a collection of routines to perform serial port I/O without ' using the Microsoft Comm Control component.
This module uses the Windows API ' to perform the overlapped I/O operations necessary for serial communications. ' ' The routine can handle up to 4 serial ports which are identified with a ' Port ID.
An example how to place the StrokeReader serial port ActiveX on Excel 2007 spreadsheet and read data from RS232 port, USB barcode scanner or RS485-equipped weighting machine. Setting up the developer tab. Run Microsoft Excel 2007. To show the Developer tab on Ribbon, click on the Office Button and then on Excel Options: 3. Nov 2, 2015 - Maurizio from Electronics Open Source at dev.emcelettronica.com discuss how we can do serial communication in Excel environment.
' ' All routines (with the exception of CommRead and CommWrite) return an error ' code or 0 if no error occurs. The routine CommGetError can be used to get ' the complete error message. Code: Private Declare Function ClearCommError Lib 'kernel32' (ByVal hFile As Long, lpErrors As Long, lpStat As COMSTAT) As Long ' ' Closes an open communications device or file handle. ' Private Declare Function CloseHandle Lib 'kernel32' (ByVal hObject As Long) As Long ' ' Creates or opens a communications resource and returns a handle ' that can be used to access the resource.
' Private Declare Function CreateFile Lib 'kernel32' Alias 'CreateFileA' (ByVal lpFileName As String, ByVal dwDesiredAccess As LongByVal dwShareMode As Long, lpSecurityAttributes As AnyByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As LongByVal hTemplateFile As Long) As Long ' ' Directs a specified communications device to perform a function. ' Private Declare Function EscapeCommFunction Lib 'kernel32' (ByVal nCid As Long, ByVal nFunc As Long) As Long ' ' Formats a message string such as an error string returned ' by anoher function. ' Private Declare Function FormatMessage Lib 'kernel32' Alias 'FormatMessageA' (ByVal dwFlags As Long, lpSource As Any, ByVal dwMessageId As LongByVal dwLanguageId As Long, ByVal lpBuffer As String, ByVal nSize As LongArguments As Long) As Long ' ' Retrieves modem control-register values. ' Private Declare Function GetCommModemStatus Lib 'kernel32' (ByVal hFile As Long, lpModemStat As Long) As Long ' ' Retrieves the current control settings for a specified ' communications device. ' Private Declare Function GetCommState Lib 'kernel32' (ByVal nCid As Long, lpDCB As DCB) As Long ' ' Retrieves the calling thread's last-error code value. ' Private Declare Function GetLastError Lib 'kernel32' As Long ' ' Retrieves the results of an overlapped operation on the ' specified file, named pipe, or communications device.
' Private Declare Function GetOverlappedResult Lib 'kernel32' (ByVal hFile As Long, lpOverlapped As OVERLAPPEDlpNumberOfBytesTransferred As Long, ByVal bWait As Long) As Long ' ' Discards all characters from the output or input buffer of a ' specified communications resource. It can also terminate ' pending read or write operations on the resource. ' Private Declare Function PurgeComm Lib 'kernel32' (ByVal hFile As Long, ByVal dwFlags As Long) As Long ' ' Reads data from a file, starting at the position indicated by the ' file pointer. After the read operation has been completed, the ' file pointer is adjusted by the number of bytes actually read, ' unless the file handle is created with the overlapped attribute.
' If the file handle is created for overlapped input and output ' (I/O), the application must adjust the position of the file pointer ' after the read operation. ' Private Declare Function ReadFile Lib 'kernel32' (ByVal hFile As Long, ByVal lpBuffer As StringByVal nNumberOfBytesToRead As Long, ByRef lpNumberOfBytesRead As LonglpOverlapped As OVERLAPPED) As Long ' ' Configures a communications device according to the specifications ' in a device-control block (a DCB structure).
The function ' reinitializes all hardware and control settings, but it does not ' empty output or input queues. ' Private Declare Function SetCommState Lib 'kernel32' (ByVal hCommDev As Long, lpDCB As DCB) As Long ' ' Sets the time-out parameters for all read and write operations on a ' specified communications device. ' Private Declare Function SetCommTimeouts Lib 'kernel32' (ByVal hFile As Long, lpCommTimeouts As COMMTIMEOUTS) As Long ' ' Initializes the communications parameters for a specified ' communications device. ' Private Declare Function SetupComm Lib 'kernel32' (ByVal hFile As Long, ByVal dwInQueue As Long, ByVal dwOutQueue As Long) As Long ' ' Writes data to a file and is designed for both synchronous and a ' synchronous operation. The function starts writing data to the file ' at the position indicated by the file pointer. After the write ' operation has been completed, the file pointer is adjusted by the ' number of bytes actually written, except when the file is opened with ' FILEFLAGOVERLAPPED. If the file handle was created for overlapped ' input and output (I/O), the application must adjust the position of ' the file pointer after the write operation is finished.
Code: '- ' CommOpen - Opens/Initializes serial port. ' ' ' Parameters: ' intPortID - Port ID used when port was opened. ' strPort - COM port name. (COM1, COM2, COM3, COM4) ' strSettings - Communication settings. ' Example: 'baud=9600 parity=N data=8 stop=1' ' ' Returns: ' Error Code - 0 = No Error.
' '- Public Function CommOpen(intPortID As Integer, strPort As StringstrSettings As String) As Long Dim lngStatus As Long Dim udtCommTimeOuts As COMMTIMEOUTS On Error GoTo RoutineError ' See if port already in use. If udtPorts(intPortID).blnPortOpen Then lngStatus = -1 With udtCommError.lngErrorCode = lngStatus.strFunction = 'CommOpen'.strErrorMessage = 'Port in use.' End With GoTo RoutineExit End If ' Open serial port. UdtPorts(intPortID).lngHandle = CreateFile(strPort, GENERICREAD Or GENERICWRITE, 0, ByVal 0&, OPENEXISTING, FILEATTRIBUTENORMAL, 0) If udtPorts(intPortID).lngHandle = -1 Then lngStatus = SetCommError('CommOpen (CreateFile)') GoTo RoutineExit End If udtPorts(intPortID).blnPortOpen = True ' Setup device buffers (1K each). LngStatus = SetupComm(udtPorts(intPortID).lngHandle, 1024, 1024) If lngStatus = 0 Then lngStatus = SetCommError('CommOpen (SetupComm)') GoTo RoutineExit End If ' Purge buffers. LngStatus = PurgeComm(udtPorts(intPortID).lngHandle, PURGETXABORT Or PURGERXABORT Or PURGETXCLEAR Or PURGERXCLEAR) If lngStatus = 0 Then lngStatus = SetCommError('CommOpen (PurgeComm)') GoTo RoutineExit End If ' Set serial port timeouts.
With udtCommTimeOuts.ReadIntervalTimeout = -1.ReadTotalTimeoutMultiplier = 0.ReadTotalTimeoutConstant = 1000.WriteTotalTimeoutMultiplier = 0.WriteTotalTimeoutMultiplier = 1000 End With lngStatus = SetCommTimeouts(udtPorts(intPortID).lngHandle, udtCommTimeOuts) If lngStatus = 0 Then lngStatus = SetCommError('CommOpen (SetCommTimeouts)') GoTo RoutineExit End If ' Get the current state (DCB). LngStatus = GetCommState(udtPorts(intPortID).lngHandleudtPorts(intPortID).udtDCB) If lngStatus = 0 Then lngStatus = SetCommError('CommOpen (GetCommState)') GoTo RoutineExit End If ' Modify the DCB to reflect the desired settings.
LngStatus = BuildCommDCB(strSettings, udtPorts(intPortID).udtDCB) If lngStatus = 0 Then lngStatus = SetCommError('CommOpen (BuildCommDCB)') GoTo RoutineExit End If ' Set the new state. Code: '- ' CommClose - Close the serial port. ' ' Parameters: ' intPortID - Port ID used when port was opened. ' ' Returns: ' Error Code - 0 = No Error.
Code: '- ' CommGetLine - Get the state of selected serial port control lines. ' ' Parameters: ' intPortID - Port ID used when port was opened. ' intLine - Serial port line. CTS, DSR, RING, RLSD (CD) ' blnState - Returns state of line (Cleared or Set). ' ' Returns: ' Error Code - 0 = No Error.
Problem scenario: The lab equipment has a RS232 port, which outputs a measurement data every 10 minutes. I would like to use parsed values in forms, formulas and my VBA logic in a Microsoft Excel spreadsheet. Requirements:.
Advanced Serial Data Logger Professional, Enterprise or Trial version. ASCII Data Query and Parser, DDE Server It is assumed that: You've configured communication parameters (baud rate, number of data bits, flow control etc) in the data logger and can receive any data without communication errors. You can work in Microsoft Excel, specifically create and use forms. Solution: Note: This tutorial is based on the previous tutorial named '. This example will use the parser settings and the data example from this tutorial.
Therefore you may read it before continue. The parser is ready we've tested it in the previous tutorial. If the parser had been correctly set up, then you should see all parser items names and their values (fig.1) in the DDE server window below. Serial port data, Excel and DDE. DDE server window. All parser items are now ready for usage from Microsoft Excel. The variable name is displayed in the 'Name' column.
You should know the DDE server name and the topic name to retrieve values via DDE. This information exists on the following page (fig.2 pos.1). Please, note that the server name may be different on your PC. Serial port data, Excel and DDE. DDE server parameters. Click the 'OK' button and close the DDE Server plug-in configuration window and the 'OK' button in the options window.
Because we want to draw a chart in a real-time environment then we should prepare an Excel file before the next step. In this example we've created a diagram with 3 plots. Each plot is based on a data from columns A, B or C. We'll place FLOW1, VOLW1 and TEMP1 to a corresponding column and the chart will be automatically updated every time. We'll use last 30 values on the diagram only.
In this example, we've placed 3 links to DDE items to cells D21-F21 like: '=testcore ddesrv!srviFLOW1' (w/o quotes) (fig.3 and 4). As you can see, the server name, the topic name and the variable name are specified in this DDE link. Serial port data, Excel and DDE. After you've placed the DDE link to the spreadsheet Microsoft Excel will start the data logger and retrieve the data automatically. If the data logger is already started then Microsoft Excel will try to read the data from the existing instance. If the data logger didn't receive and didn't parse the data yet, then Microsoft Excel will display a text about the broken link, but will read a value after successful parsing.
We are now ready for creating a VBA code, which will process the DDE data. This code will copy DDE values to columns, move old values and add rows to the history list (fig.4, 5). Serial port data, Excel and DDE. Source spreadsheet. The figure above contains following elements:. The DDE data will be placed here and it is a source for plots in this example;.
Diagram;. History list. VBA code from the Excel file.