Could not load file or assembly Microsoft.Office.BusinessApplications.Fba VSTO error

Here is an error that plagued me (occasionally) for months.  Since Office 2013 was rolled out, we would sometimes get a call from a user trying to install an older Visual Studio Tools for Office add-in. Not sure how much of the specifics are at play here, but these were the facts in this situation:

Windows version: Windows 7
New Office version: Office 2013
VSTO Add-In version: Office 2007
VSTO Add-In application: Excel

The full text of the error was:

The value of the property 'type' cannot be parsed.  The error is: Could not load file or assembly 'Microsoft.Office.BusinessApplications.Fba, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9r29c' or one of its dependencies. The system cannot find the file specified. (C:\Program Files (x86)\Common Files\Microsoft Shared\VSTO\10.0\VSTOInstaller.exe.Config line 10)

For months, all I could find was this post suggesting to repair Office.  Unfortunately, it failed to correct the issue and we were forced to uninstall and reinstall Office 2013 on a number of machines.  After a successful reinstall of Office, the VSTO installation ran to completion. This is a pain for both the support personnel and the users.

Today I found and successfully used the post at the end of this thread.  My experience wasn’t quite as clean as described, but it worked.  Here’s what I did:

1. I right-clicked on setup.exe (installer for VSTO application) and chose ‘Run as administrator’. Answered [Yes] at the UAC prompt.
2. I received a message informing that I already had the VSTO application installed.  The application was not showing in Excel, however this was already a step further than previous installation attempts.
3. I pushed forward, found the VSTO application in Programs and Features and uninstalled it.
4. Finally, I ran setup.exe again - this time not using ‘Run as administrator’.  It installed successfully and runs as expected. (Plan was to use Run as admin again if it failed, but it wasn’t required.)

Hope this helps others, too.

Later.

Visual Studio OneClick Publish - Target machine actively refused it 128.30.52.37:80

The project is a VSTO Excel Add-In that retrieves additional information from web services hosted on our ERP system.  The end of this is an unresolved issue that we were able to work around by taking SharePoint out of the scenario.

One requirement: provide a deployment package that automatically installs new versions without requiring the user to manually uninstall previous versions.  Simple enough.  In the past I had always included a Visual Studio “Setup” project with my solutions.  There’s lots of flexibility there, but this was just an Excel Add-In.  I considered trying (again) Microsoft’s OneClick publishing mechanism.

I’m relatively new to OneClick.  I scanned through the documentation and configured the Publish pane to store the data onto my local hard drive with a URL off of our local SharePoint server.  Something like the following.  Side note: I tried using a WebDav UNC path to the SharePoint site, but VS2010 choked so I gave it up. Okay – I can copy the files there manually.

My project is designed to generate a prerequisite setup program for .NET Framework 4.0 Client Profile and VSTO 4 Runtime. Our SharePoint site is configured to disallow .exe files, so I zipped up my setup.exe before placing the entire installation at the SharePoint location defined above.

Without a setup.exe, this process works great.  The .vsto file is fully accessible; clicking only the .vsto file functions fine to install the Excel Add-In.

There’s a problem, however, when trying to use the setup.exe.  When using the setup.exe to initiate the installation, the following exception occurs.

************** Exception Text **************
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 128.30.52.37:80
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---
   at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.GetManifests(TimeSpan timeout)
   at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.InstallAddIn()

Researching online, I was able to find one reference to a deployment architecture similar to ours.  Here, someone on the Nintex forum discusses the same error within SharePoint.  Unfortunately no solution is available there.

Conjecture: It appears that while the .vsto file is being processed on the server an XML validation is failing.  Could the failure be due to security implemented on the server to disallow connections outside the firewall – in this case to hans-moleman.w3.org?  If that error happens under the watchful eye of the setup.exe, maybe the installation fails.  Working with our LAN team, we removed the “no external HTTP requests” restriction on the SharePoint server, but the problem was not resolved.

Supposing that SharePoint may be the culprit, I reconfigured the Publish pane to use a URL straight from an IIS server – no SharePoint involved.

From this, I received two benefits.  First, I was able to move the entire installation to the web server share without having to first zip the setup.exe.  Second, and most important, the setup program functioned without error.  When SharePoint is not included and setup.exe accesses the .vsto file from IIS, then whatever caused the request to 128.30.52.37:80 either

• does not occur or
• does not fail or
• the exception is not passed back to the setup program.

For us here, using IIS instead of SharePoint (essentially avoiding the problem) is a fine solution and we intend to make this the direction going forward for OneClick-published installation packages.  However – if anyone has additional information about the error or can propose a different solution, I would be interested in hearing it.

Jeff

Excel's Extended Worksheet from Native Worksheet

The Setup
I recently had a request to create a simple way (user's perspective) to access and modify a SQL serer table directly without having to connect via the SQL Server Manager and navigating to the Database > Table > Edit.  After giving it some thought I decided to use VSTO to create an Excel interface to the three tables we needed to connect to.


This turned out to be an excellent choice.  90% of this project was simply "plumbing".  Hooking up the TableAdapter to the tables, generating a couple Get methods, an Update and a Delete method.  Finally implementing the SelectionChange event to determine when to update the record.


The hang-up came when I attempted to make use of the Ribbon Designer to provide a couple of user-interface options: one to switch from test to production systems and another to delete the selected record.  The system change was fairly simple once I learned how to export the Ribbon XML and implement the Callback method for a RibbonComboBox control:
  public void comboSystem_TextChanged(Office.IRibbonControl control, string text)

For a full list of prototypes for various Ribbon control events see the list at MSDN here.

The real problem was with the Delete routine.  With three different worksheets that could potentially have been active I needed some way to determine on which table to call the DELETE statement.  I started by creating a C# Interface by which my VSTO Worksheets must abide: ITableSheet that contained just two methods (RefreshData and DeleteRecord) and implemented them appropriately for each of the three Worksheets.  I thought that if I could access the ActiveSheet from the RibbonButton click event, then of course I could simply cast it to an ITableSheet and call DeleteRecord.  This provides an easily scalable way of adding new tables later, should that be necessary.

The Problem

Here's where I learned the difference between the native worksheet (Microsoft.Office.Interop.Excel.Worksheet) and the extended worksheet (Microsoft.Office.Tools.Excel.Worksheet).  It so happens that the ActiveSheet property is a native Worksheet object.  This is not easily cast to a VSTO (extended) Worksheet object.

And here's where the solution gets a little messy.

VSTO 2005: there's an alleged "unsupported" way to make this conversion.  That is to add a method called "GetExtendedWorksheet" to your project.  You can see a discussion on the topic here.  And you can find an example use of it here.

VSTO 2008: Unfortunately, Microsoft prevented this method by removing access to the RuntimeCallback object in VSTO 2008.  This is the version of VSTO that I was using.  A short discussion can be found here.

The Solution (for now)
The work-around that I implemented was to create an ActiveTableSheet property in my ThisWorkbook of type ITableSheet.  When a new worksheet becomes active I manually set the ActiveTableSheet.  Then during the RibbonButton click event, I can access Globals.ThisWorkbook.ActiveTableSheet.DeleteRecord.


The Real Solution (VS 2010)
The good news is that Microsoft appears to be providing a fully supported method of converting the native worksheet to an extended worksheet in VS 2010.  (This is beta and is subject to change.)