Yoctopuce support regularly receives questions about using Yoctopuce devices from an Excel spreadsheet. As Office versions change regularly, it is time to update our information on this subject.
A few years ago, we documented two ways to access Yoctopuce modules:
- By creating a custom add-in, written in C#, that installs in Excel and adds new VBA functions to access Yoctopuce modules;
- Using a Yoctopuce RTD server, which enables you to get values that change in real time directly in the spreadsheets. The small, relatively generic RTD server provided as an example was updated some time after the original post was published.
When we wrote those posts, Office 365, Microsoft 365 and Microsoft 365 for the web did not yet exist, so they are not mentioned. So, do these integration methods still work with the new versions of the Microsoft suite?
Use on a local Windows computer
If you use the Excel application installed directly on your computer, whether with an Office 2021 perpetual license or a Microsoft 365 subscription, you can still use the integration methods described in the posts cited above: they are still supported on the 2023 versions of Office. There are just a few differences for the installation process.
It is no longer necessary to put the Add-in files in a particular directory of Excel. If you want to use the Yoctopuce RTD server, download the zip file of the new version we just recompiled with the updated libraries, open the archive and put all the files in a directory of your choice, for example the same directory where you save your Excel workbook. Then, to use it,
- Launch Excel and open the worksheet where you want to use the Yoctopuce modules
- Use the menu File, Open to open the YoctoRTD2023-AddIn64-packed.dll file that you have extracted from the archive. Excel then tells you that the add-in is not signed:
Warning that the add-in isn't signed
Click the button on the left, "Activate this add-in for this session only". - At this point, a new Add-ins menu has just appeared in Excel. There you can find a Yoctopuce Sensor RTD Server section that allows you to display the console that tracks Yoctopuce module activities.
- In a cell of your table, type the formula =YSensorValue("any"). If you have a Yoctopuce sensor connected, its value appears in the table, and is automatically and continuously refreshed.
Fun, isn't it ?
Most portable use
If you don't want to, or can't, install the Yoctopuce add-in, for example because you use Excel on macOS or Microsoft 365 for the web, there is now a new way to access Yoctopuce modules: Office Script.
Office Script allows you to create small programs that can update data in your Excel workbook. So these are not functions that you can use in Excel formulas, but rather macros that can be run at a given time, for example by pressing a button to automatically fill cells. Here is an example of an Office Script function that inserts in the current sheet the current value of all the functions of detected Yoctopuce modules:
{
let values: YPRecs = await getAdvertisedValues();
// Create a table in the Excel sheet with light sensor values
let sheet = workbook.getActiveWorksheet();
let headers: string[][] = [["Type", "HardwareId", "LogicalName", "Value"]];
let headerRange = sheet.getRange("B2:E2");
headerRange.setValues(headers);
headerRange.getFormat().getFill().setColor("#4472C4");
headerRange.getFormat().getFont().setColor("white");
// Create the product data rows.
let sensorData: (string | number)[][] = [];
for (let functionType in values) {
for (let rec of values[functionType]) {
sensorData.push([functionType,
rec.hardwareId, rec.logicalName, rec.advertisedValue])
}
}
let dataRange = sheet.getRange("B3:E" + (3 + sensorData.length - 1));
dataRange.setValues(sensorData);
}
This script uses a getAdvertisedValues() function, which we need to define to access the state of the Yoctopuce modules. However, although based on TypeScript, Office Script does not allow you to directly include the Yoctopuce programming library, because Microsoft has voluntarily restricted the language to avoid introducing security breaches. Nevertheless, it is possible to make HTTP requests to access the REST interface of Yoctopuce modules using the fetch() function. Here is how to read the global state of Yoctopuce functions through the REST interface of VirtualHub:
{
// Retrieve published function values from VirtualHub
let fetchResult = await
fetch('http://127.0.0.1:4444/api/services/yellowPages.json');
return await fetchResult.json();
}
// declare the structure of the JSON data
interface YPRecs
{
[serviceName: string]: YPRecord[]
}
interface YPRecord
{
baseType: number;
hardwareId: string;
logicalName: string;
advertisedValue: string;
index: number
}
We can then easily create a button that launches the script, and we get the following result:
Measures retrieved by Office Script
The advantage of this method is that it is extremely portable: you could even use it in the middle of nowhere with Microsoft 365 for the web, in combination with VirtualHub for web, to include data sent by a YoctoHub-GSM-4G in your Excel sheet.
On the other hand, it is currently not possible to use Office Script to continuously update a cell, as you can do with the RTD server: for the moment, an Office Script macro must be launched by a click, and terminate after a certain time.
