Excel and Yoctopuce devices

Excel and Yoctopuce devices

Not so long ago, someone asked us if our devices could be driven directly from Microsoft Excel. Actually the answer is yes. But, unfortunately, this not as easy to do as we like our libraries to be. Anyway, seeing Excel plotting data coming directly from a Yoctopuce sensor might be worth the effort.




Usually, Excel can be controlled with an internal scripting language called VBA, which is a variant of VB6. Since Microsoft killed VB6 the day they decided to stop selling VB6 licenses, there is no VB6 API for Yoctopuce devices. And it is very unlikely that there will be one someday, same for VBA.

Can Yoctopuce sensor data be read directly from Excel?
Can Yoctopuce sensor data be read directly from Excel?


Anyway, if you are not afraid of programming stunts, it's possible to write some .NET code and to call it from VBA. There are a few ways to achieve this. We chose to demonstrate this using the excellent Excel-DNA software which is free, open source, and works pretty well.

The idea is to write a C# .NET DLL containing all the required code for Yoctopuce devices to work. Then to build an Add-in, which, once loaded in Excel, provides access to Yoctopuce device features. Let's have a closer look on the required code. If you are too impatient, there is a video of the result at the end of the article.

Step 1: download
Download the Excel-DNA files and the Yoctopuce library for C#, unzip both wherever you want.

Step 2: structure
Start Visual C#, create a new "Class library project". Add to your project the source files needed for your Yoctopuce device to work, as well as the yapi.dll file. Copy the ExcelDna.xll file from the Excel-DNA folder and rename it to the same name you used for your assembly, YoctoExcel.xll for instance. Add a reference to ExcelDna.Integration. You can rename the Class1.cs stub to YoctoExcel.cs if you want.

Add a new text file named YoctoExcel.dna containing a description for your Excel add-in.


<DnaLibrary Name="Yoctopuce Excel demo" RuntimeVersion="v4.0">
  <ExternalLibrary Path="YoctoExcel.dll" />
</DnaLibrary>
 



Don't use just any value for the name attribute in the YoctoExcel.dna file, this attribute is used later. Make sure the files yapi.dll, YoctoExcel.xll, and YoctoExcel.dna are copied to the output directory by editing the "copy to Output Directory" property.

Now, if everything went well, your project structure should look like this:

Structure of the C# .NET project
Structure of the C# .NET project



Step 3: .NET code
Now you have to code the Yoctopuce features you need in the YoctoExcel.cs file. But before that, you need to add the code required for the Add-in registration in Excel. This code is a bit cryptic, just copy and paste it at the beginning of the file.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using ExcelDna.Integration.Extensibility;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using System.Runtime.InteropServices;
using System.Windows.Forms;

// some cryptic stuff to register the addIn in Excel
public class AddInRoot : IExcelAddIn
 {
  public void AutoOpen()
    {  try
       { var com_addin = new AddInComRoot();
         com_addin.GetType().InvokeMember("DnaLibrary", BindingFlags.NonPublic | BindingFlags.Instance |
         BindingFlags.SetProperty, null, com_addin, new object[] { DnaLibrary.CurrentLibrary });
         ExcelComAddInHelper.LoadComAddIn(com_addin);
       }
      catch (Exception e)
       {   MessageBox.Show("Error loading COM AddIn: " + e.ToString());
       }
     }

     public void AutoClose() { }
  }


[ComVisible(true)]
public class AddInComRoot : ExcelDna.Integration.CustomUI.ExcelComAddIn
  {  // : IDTExtensibility2, ie COM "AddIn".ExcelDNA finds this by magic.
     MyAddinObject _helper;

    public AddInComRoot() {}

    public override void OnConnection(object Application,
    ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
      {  _helper = new MyAddinObject();

         AddInInst.GetType().InvokeMember("Object",
                BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty,
                null,AddInInst,new object[] { _helper });
       }
     public override void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom) {}
     public override void OnAddInsUpdate(ref Array custom) {}
     public override void OnStartupComplete(ref Array custom) {}
     public override void OnBeginShutdown(ref Array custom) {}
    }

 



Then add the code you want to make accessible from Excel. Here is a very basic handling of the "temperature" feature.


[ComVisible(true)]
public class MyAddinObject
  {   // This becomes the VBA addin.Object
      string lasterrmsg = "";
      YTemperature tsensor;
      bool initdone = false;

      // init the Yoctopuce API and search for a temperature sensor
      public bool Init()
      {
        if (initdone) return true;
        if (YAPI.RegisterHub("usb",ref lasterrmsg) == YAPI.SUCCESS)
         {
            tsensor = YTemperature.FirstTemperature();
            if (tsensor == null)
            {
              lasterrmsg = "No temperature sensor found";
              return false;
            }
            initdone = true;
            return true;
         }
         return false;
      }

      // return the last encountered error message
      public string getLastError()
      {
        return lasterrmsg;
      }

      // return the found temperature sensor  name
      public string getSensorName()
      {
        if (tsensor == null) return "invalid temp sensor";
        return tsensor.describe();
      }

      // return the value reported by the sensor
      public string getTemperature()
      {
        if (tsensor == null) return "invalid temp sensor";
          return Convert.ToString(tsensor.get_currentValue());
      }
   }
 



Compile, and that's it. The .NET part is ready, the output folder should then look like this:

The Excel add-in files
The Excel add-in files



step 4: Add-in import into Excel
Open Excel, make sure macros are authorized, activate the developer part, and add your Add-in using the Add-ins and browse buttons, then chose the YoctoExcel.xll file.

Add your add-in into Excel (sorry form the French user interface)
Add your add-in into Excel (sorry form the French user interface)



Step 5: VBA code
Almost done! Now you need some VBA code to look for your add-in among the available ones, please note that this uses the description you defined in the YoctoExcel.dna file.


Dim YoctoAddin As Object

Sub init()
    Dim cai As COMAddIn
    For Each cai In Application.COMAddIns
        ' Could check cai.Connect to see if it is loaded.
        Debug.Print cai.Description, cai.GUID
        If InStr(cai.Description, "Yoctopuce Excel demo") Then
            Set YoctoAddin = cai.Object
            If YoctoAddin Is Nothing Then
              Debug.Print "ObjNothing"
            Else
              If YoctoAddin.init() Then
                Debug.Print "init sucessfull"
              Else
               MsgBox (YoctoAddin.getLastError())
              End If
            End If
        End If
    Next
End Sub
 



Done! Now you have what you wanted: the YoctoAddin matches the MyAddinObject you defined in the YoctoExcel.cs file. You can use any of its methods to achieve whatever you want. You can find here a full example made with visual C# 2010 and working with Excel 2010. This demo plots a temperature graph in real time, you even can see it working in this video.

  



If you want to know more, just have a look at the Excel-DNA web site. You can find there a lot of information, including explanations on how to pack your Add-In in one single file, making it easier to distribute. Have fun.

Add a comment 2 comments
Back to blog



1 - martinm (Yocto-Team)Friday,december 27,2013 7H51

A customer just gave us a link to a useful Excel Macro written by Joacim Andersson from Brixoft Software. That macro implements system calls, making the use of the Yoctopuce command line API from Excel quite easy. Here is an application example with the Yocto-Light.

http://www.yoctopuce.com/EN/downloads/yoctolightDemoExcel.zip

2 - chan Wednesday,april 13,2016 8H32

Nice!!

Im not a programming person, but i would like to ask whether its possible to get delay temperature value let say by 1 minute. Meaning, in this moment, the temperature value displayed is 1 minute expired instead of current one.

Thanks

Yoctopuce, get your stuff connected.