Arduino Playground is read-only starting December 31st, 2018. For more info please look at this Forum Post

Arduino Serial Data Directly Into Microsoft Excel

Last Modified: November 28, 2016, at 010:16 AM
By: dndubins
Platforms: Microsoft Excel 2010 (Mac and PC versions)

Although 3rd party applications and plugins are available for port logging, it is possible to log data directly into Microsoft Excel through Visual Basic.

Link to worksheet: http://individual.utoronto.ca/ddubins/GetCSV.xlsm

Introduction

There are no lack of applications tailored towards saving a .CSV file from an open port, which can then be imported into Microsoft Excel. For instance, GoBetwino, Processing, and Eltima Port Logger are useful and implementable strategies.

However, it is possible to have Visual Basic (underneath Excel) listen to the serial port and output the data directly into Excel without requiring another program or plugin to load - in both Mac and PC versions.

The first order of business is finding out the open port number, through the Arduino IDE. This is done by loading the Arduino IDE, connecting your device (e.g. Arduino Uno), then clicking on Tools --> Port.

Visual Basic Macros

For Excel in Windows:

Next, you will need to create a macro in Visual Basic to read the port. In Windows, the stripped down core routine looks like this:

  1. Sub ReadCommPC()
  2.     Dim COMfile As Integer
  3.     Dim COMstring As Variant
  4.     Dim baudrate As Long
  5.     Dim record_cat As Variant
  6.     Dim record as String * 1
  7.     Dim emptyRecord As String * 1
  8.  
  9.     COMport = "COM4"   'Enter the COM port here.
  10.    baudrate = 9600       'Enter the baud rate here.
  11.  
  12.      'Open COM port with baud rate, no parity, 8 data bits and 1 stop bit
  13.    COMfile = FreeFile
  14.     COMstring = COMport & ":" & baudrate & ",N,8,1"
  15.  
  16.     Open COMstring For Random As #COMfile Len = 1
  17.     record = ""
  18.     record_cat = ""
  19.  
  20.     Do
  21.         DoEvents   'Don't lock up excel while waiting
  22.        Get #COMfile, , record      'data is read in 1 character at a time
  23.            If record <> "," And Asc(record) <> 13 And Asc(record) <> 10 And record <> emptyRecord Then
  24.                 record_cat = record_cat & record
  25.             End If
  26.             If Asc(record) = 13 Then   ' if carriage return
  27.                MsgBox (Trim(record_cat))
  28.                 record_cat = ""
  29.                 record = ""
  30.             ElseIf record = "," Then     ' if comma
  31.                MsgBox (Trim(record_cat))
  32.                 record_cat = ""
  33.             End If
  34.         Sleep 20
  35.     Loop
  36.     Close #COMfile
  37. End Sub
[Get Code]

Visual Basic connects directly to the COM port. This version of the macro only prints a message of the data, rather than the spreadsheet. However, going to the spreadsheet from this format is easier with commands like: 

  ActiveCell.Offset(1,0).Value = record_cat

For Excel in OS X:

For the Mac version of Excel, things are a little more complicated. The Open COMstring command won't work. However, Visual Basic allows for shell commands. One strategy then is to call the following command in a shell: 

  head -1 /dev/cu.yourcommunicationsportgoeshere

This gets the first line of your port from the terminal prompt. Then, since calling the head command will reset the serial connection each time, for the Uno it was necessary to connect a 10 uF capacitor between RST and GND.

Here is a stripped down version of the Visual Basic macro for Mac:

  1. Public Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
  2. Public Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
  3. Public Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
  4. Public Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long
  5.  
  6. Sub ReadCommMac()
  7.     Dim COMport As String
  8.     Dim file As Long
  9.     Dim COLindex As Integer
  10.     Dim ROWindex As Integer
  11.     Dim record As String
  12.     Dim chunk As String
  13.     Dim temp1 As String
  14.     Dim char1 As String
  15.     Dim read As Long
  16.     record = ""
  17.     COLindex = 0
  18.     ROWindex = 0
  19.     stopclick = False
  20.  
  21.     COMport = "/dev/cu.wchusbserialfd130"  ' Enter the serial port here
  22.  
  23.     Do
  24.         DoEvents 'Don't lock up Excel while running
  25.        file = popen("head -1 " + COMport, "r")
  26.         If file = 0 Then
  27.             Exit Sub
  28.         End If
  29.         read = 0
  30.         record = ""
  31.         While feof(file) = 0
  32.             chunk = Space(50)
  33.             read = fread(chunk, 1, Len(chunk) - 1, file)
  34.             If read > 0 Then
  35.                 chunk = Left$(chunk, read)
  36.                 record = record & chunk
  37.             End If
  38.             temp1 = ""
  39.             For i = 1 To Len(record)
  40.                 char1 = Mid(record, i, 1)
  41.                 If char1 = "," Then ' Comma separated value
  42.                    MsgBox(temp1)
  43.                     temp1 = ""
  44.                 ElseIf Asc(char1) = 13 Then  ' new line
  45.                    MsgBox(temp1)
  46.                     temp1 = ""
  47.                 Else:
  48.                     temp1 = temp1 + char1
  49.                 End If
  50.             Next
  51.         Wend
  52.         exitCode = pclose(file)
  53.     Loop
  54. End Sub
[Get Code]

Notes

Setting a faster baud rate in the PC macro works well. However, I was unsuccessful in using a baud rate other than 9600bps with the Mac macro, even with the stty command in the terminal window.

To do

The code is a bit clumsy, and could use optimizing. Also, if you plan on using this code, you can also have the program save the workbook every once in a while (e.g. every 10 readings?) with the command: 

    ActiveWorkbook.Save