VBA for Bluetooth Port communication Hi With my project I want to use Excel 2000 to communicate with Arduino microcontroller by sending small amounts of single numeric data via bluetooth port.
Last Modified: November 28, 2016, at 010:16 AM
By: dndubins
Platforms: Microsoft Excel 2010 (Mac and PC versions)
By: dndubins
Platforms: Microsoft Excel 2010 (Mac and PC versions)
![Software Software](https://www.robotshop.com/community/forum/uploads/default/original/2X/3/304eadbbfd4d76f3862ae49b02fe3ac0612228e8.jpeg)
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:
- Dim COMfile AsInteger
- Dim baudrate AsLong
- Dim record asString * 1
- baudrate = 9600 'Enter the baud rate here.
- 'Open COM port with baud rate, no parity, 8 data bits and 1 stop bit
- COMstring = COMport & ':' & baudrate & ',N,8,1'
- Open COMstring ForRandomAs #COMfile Len = 1
- record_cat = '
- Do
- Get #COMfile, , record 'data is read in 1 character at a time
- If record <> ','And Asc(record) <> 13 And Asc(record) <> 10 And record <> emptyRecord Then
- EndIf
- MsgBox (Trim(record_cat))
- record = '
- MsgBox (Trim(record_cat))
- EndIf
- Loop
- EndSub
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:
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:
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:
- PublicDeclareFunction popen Lib 'libc.dylib' (ByVal command AsString, ByVal mode AsString) AsLong
- PublicDeclareFunction pclose Lib 'libc.dylib' (ByVal file AsLong) AsLong
- PublicDeclareFunction fread Lib 'libc.dylib' (ByVal outStr AsString, ByVal size AsLong, ByVal items AsLong, ByVal stream AsLong) AsLong
- PublicDeclareFunction feof Lib 'libc.dylib' (ByVal file AsLong) AsLong
- Sub ReadCommMac()
- Dim file AsLong
- Dim ROWindex AsInteger
- Dim chunk AsString
- Dim char1 AsString
- record = '
- ROWindex = 0
- COMport = '/dev/cu.wchusbserialfd130'' Enter the serial port here
- Do
- file = popen('head -1 ' + COMport, 'r')
- ExitSub
- read = 0
- While feof(file) = 0
- read = fread(chunk, 1, Len(chunk) - 1, file)
- chunk = Left$(chunk, read)
- EndIf
- For i = 1 To Len(record)
- If char1 = ','Then' Comma separated value
- temp1 = '
- MsgBox(temp1)
- Else:
- EndIf
- Wend
- Loop
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:
Excel based oscilloscope using Arduino and VB.NET
Demonstration at: https://www.youtube.com/watch?v=hTaZTMWb7BE
The 'CerealCom' (as in Serial Communication system) interfaces an arduino board with Microsoft Excel.The purpose of this project was to make a basic digital oscilloscope using cheap hardware and microsoft excel.There are three stages tothis project:
- The Arduino hardware:The Arduino Uno is a microcontroller with hardware input and output pins. The programming of this board is done through thearduino environment using C. I have defined a specific pin for the voltage input on the board. In the development framework,I have also defined the baud rate at which data is being streamed into the USB COM port.
- Visual Basic .NET 4.0
This part was required so that the voltage data being streamed into the COM port of the desktop is being properly used andpresentetd. I used A console application in VB.NET to interface to the port, and manually handled the data. I would then needa predefined excel document that has a presentation format set up, so that the user of the system will know where to lookin order to read his/her measurements.
- The MS Excel template file
This file is merely used so that the output from the VB.NET application ('Asillyscope.vb') has a target container to dump the data to.
Before using CerealCOm, you first have to calibrate the timing. In order to do this, you have to use a precise capacitor and aprecise resistor, which would give you a relaxation circuit whose relaxation time is easy to calculate. this time T = R*C, whereR is the resistance, ance C is the capacitance. Since we know that when the capacitor and resistor are in parallel, and they areenergized, it takes the circuit exactly T seconds to go from the stimulation voltage, to exactly half that. USing this knowledgewe can calibrate the timing. This setup is made possible using the 'captester.xls' and 'captester.ino'.