Problem automatically re-registering XLL using FileSystemWatcher

[ben j]

We have some F# UDFs created using Excel-DNA's CustomRegistration. I'm trying to get a workflow where we can modify the F# source files in visual studio, hit recompile, and see the changes in Excel without manually reloading anything.

F# doesn't support Edit and Continue, so my approach is to create a separate add-in which uses a FileSystemWatcher to watch for changes and reload the main add-in whenever necessary.

The problem I'm having is that the call to XlCall.Excel(XlCall.xlfUnregister...) is failing with an exception. Even though the re-register seems to succeed, the UDFs haven't updated, so I'm guessing the failed Unregister prevented Excel from seeing the new code. It works fine if I do it by hand with File->Open->/path/to/xll

I'm probably doing something stupid. Here's the add-in code for the autoloader... any ideas appreciated...

Also, I'm slightly dubious of the threadsafety of doing this inside a FileSystemWatcher event. Are there any problems you can foresee with this?

<DnaLibrary Name="AutoLoader" Language="C#" RuntimeVersion="v4.0"> 

<Reference Name="System.Windows.Forms" /> 

<![CDATA[ 

using System; 

using System.IO;

using System.Windows.Forms; 

using ExcelDna.Integration; 

// This class implements the ExcelDna.Integration.IExcelAddIn interface.

// This allows the add-in to run code at start-up and shutdown.

public class AutoLoaderAddIn : IExcelAddIn

{

  private static object _registerId; 

  private static FileSystemWatcher _watcher;

  private static string _xllDir = @"/path/to/bin";

  private static string _xllFile = @"NameOfAddinWithoutExtension";

  public void AutoOpen()

  {

    Register();

    // Create a new FileSystemWatcher and set its properties.

    _watcher = new FileSystemWatcher();

    _watcher.Path = _xllDir;

    _watcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite

        | NotifyFilters.FileName | NotifyFilters.DirectoryName | NotifyFilters.Attributes 

        | NotifyFilters.Size;

    _watcher.Filter = _xllFile + "-packed.xll";

    // Add event handlers.

    _watcher.Changed += new FileSystemEventHandler(OnChanged);

    _watcher.Created += new FileSystemEventHandler(OnChanged);

    _watcher.Deleted += new FileSystemEventHandler(OnChanged);

    _watcher.Renamed += new RenamedEventHandler(OnRenamed);

    // Begin watching.

    _watcher.EnableRaisingEvents = true;

  }

    

  private static void OnChanged(object source, FileSystemEventArgs e)

  {

    Unregister();

    Register();

  }

  private static void OnRenamed(object source, RenamedEventArgs e)

  {

    Unregister();

    Register();

  }

    

  private static void Register()

  {

//MessageBox.Show("About to register."); 

    if(_registerId == null)

    {

      try

      {

        _registerId = XlCall.Excel(XlCall.xlfRegister, _xllDir + @"\" + _xllFile + ".xll"); 

      }

      catch(Exception e)

      {

//MessageBox.Show("Register exception " + e.Message); 

      }

    }

  }

  

  private static void Unregister()

  {

//MessageBox.Show("About to unregister."); 

    if(_registerId != null)

    {

      try

      {

        object success = XlCall.Excel(XlCall.xlfUnregister, _registerId); 

        _registerId = null;

      }

      catch(Exception e)

      {

//MessageBox.Show("Unregister exception " + e.Message); 

      }

    }

  }

    

  public void AutoClose()

  {

    Unregister();

  }

}

]]> 

</DnaLibrary> 

[Govert van Drimmelen]

Hi Ben,

In general, calling xlfRegister on an add-in that is already loaded should do an unregister and re-register in the same way as File->Open does.

On the other hand, calling xlfUnregister is not enough to actually remove an .xll add-in (due to some Excel bugs, and some Excel-DNA implementation details). For this you need to call ExcelIntegration.UnregisterXLL(pathToXLL) instead.

For the XlCall.xlfRegister and UnregisterXLL calls to work, the calls should happen not only on the main thread (which must be the case for your event handlers already otherwise XlCall.Excel will always fail) but also in a macro context (where Excel is calling you as a macro).

The easiest way to ensure you're in a macro context like this, is to to run the code via ExcelAsyncUtil.QueueAsMacro. So you might have:

    ExcelAsyncUtil.QueueAsMacro( () =>

    {   

        string xllPath = ...

        // ExcelIntegration.UnregisterXLL(xllPath); // Should not need this if you are re-registering...

        ExcelIntegration.RegisterXLL(xllPath);

    });

Let us know whether this works....

Regards,

Govert

[ben j]

Awesome, thanks. Works a treat.

Just so I understand, please can you explain the difference between the addin.xll file and the addin-packed.xll file.

It seems that the addin.xll file is the one I register, but the addin-packed.xll file is the one that gets rebuilt by visual studio... is that right?

[Govert van Drimmelen]

Hi Ben,

add.xll is just a copy of the ExcelDna.xll file in the distribution. It is loaded as an add-in together with addin.dna, and whatever is specified in addin.dna as "ExternalLibrary"s - typically the addin.dll that is compiled in the project.

So for the simplest case, your add-in needs the three files addin.xll, addin.dna and addin.dll, where addin.dll is rebuilt every time you compile.

Excel-DNA has an extra feature, allowing all the files (those three, and additional references, image files etc.) to be packed inside the .xll and extracted at runtime. The utility program ExcelDnaPack.exe does this packing. With the NuGet package, things are set up to automatically run the packing utility, and the output of this is add-packed.xll. This differs from addin.xll in that it is a standalone add-in, requiring no other files.

So for a simple case you should be able to copy addin-packed.xll to an empty directory, and it should open and work as a single-file Excel add-in. By adding extra instructions to the .dna file, you can have some of your extra references also packed into the .xll file.

The idea is that if you have to redistribute your add-in, you can just give a user the single addin-packed.xll file (maybe also the addin64-packed.xll file). For normal debugging and development, you just use the addin.xll file with addin.dll. This allows debugging to work right.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ben j [b...@mostlystateless.com]
Sent: 16 June 2014 05:09 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: Problem automatically re-registering XLL using FileSystemWatcher

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.