The best thing about using Excel for data acquisition is that there are tons of resources available. Microsoft has large amounts of online data available. Run a macro when certain cells change in Excel – Office | Microsoft Docs is a great article published by Microsoft on a very simple tool. When you use this tool in combination with our MQTT to Excel software wedge, you can build into your automatic plotting routine, update Excel on MQTT change.
The thing is, you need to use your imagination when you reference these kinds of articles. The article isn’t specifically telling you how to update a chart. This specific article is telling you how to “run a macro, a macro that displays a message”. Ultimately we want to “run a macro, so that we can Update Excel on MQTT change”.
What is MQTT?
Message Queuing Telemetry Transport (MQTT) is a lightweight network protocol that can transport sensor measurements to the internet. When you use Robert Owen ROI-XCEL Cellular Signal Conditioners, MQTT is the transmission protocol used. It allows a number of data monitoring applications to be used very easily with ROI-XCEL data sources.
The format of the MQTT data can be variable. It can depend on the device, the data need, the requirements, etc. The update may contain just the time and value of the most current reading. I may contain min, max, and average values.
b’D5252,31/05/2021 12:43:36,0.000,0.000,0.000,12.259,’ is a typical simple MQTT string received by a generic ADC (take a look at it here). It contains the date, time, and a series of values. Date and time don’t need to be software calculated by the routine, because they are provided by the device.
Update Excel on MQTT change
The MQTT to Excel software wedge monitors data published on an MQTT broker. It monitors that broker’s topic the ROI-XCEL is publishing to, continually looking for new update posts, and writes those updates into Excel. It writes the data to the same cell each time. That’s it, it simply writes updates to the same cell. It’s a a very simple program to operate, but it has just 1 simple function that it executes, over and over.
It’s possible to put more automation into a customized version of the wedge program e.g., automatic row incrementing, stop/start/reset controls on the program form, threshold levels to minimize incrementing (time/value-delta/etc.). It is better to have that limitation, as it is very simple to operate. Simple to operate and still powerful when you couple it with VBA. Those customized features can also be done in VBA, which allows the wedge program to remain consistent, and simple in it’s use.
Excel VBA Macros Allow Custom Programming
The workbook below was accomplished using VBA to take the MQTT to Excel software wedge posted data, look for changes, track the last row that a change was copied to, paste the update properly parsed, and increment the row.
Every time the MQTT broker has a published device update available, the MQTT to Excel software wedge puts the value into Sheet1 range A1. Because monitoring that range for change using VBA based on the Microsoft example, allows us to call the “Public Sub record()” routine, where the string is split and the parts are placed in appropriate cells for plotting.
Using some of the other great capabilities in Excel, we added start/stop/reset functions, and a constantly rescaling graph plot of the increasing rows of data.
Experience to simplify measurement
Robert Owen has years of experience in connecting sensors and software together. Because we code custom software solutions, we can get it done. We offer the approach most suited to the project. We can get it done, regardless of size, because we have the pieces to build solutions. For monitoring anywhere from a single sensor to hundreds. Robert Owen can assist with Sensor Signal Conditioning, Sensor Signal Processing, and Sensor Data Acquisition. Parts in a range of sensor signal connection services we offer.
To learn more Contact Us we look forward to answering your questions.