CompilerParameters.ReferencedAssemblies - Adding interop assemblies (Microsoft.Office.Interop.Excel.dll)

I'm creating a method that will automatically create an Excel file. The appearance of the Excel file (format) will depend on the given VB.NET script. Now I'm having a problem with regards to referencing the assembly Microsoft.Office.Interop.Excel.dll. Here's my sample code below:

Imports System.CodeDom.Compiler
Imports Microsoft.Office.Interop

Public Class ExcelScript

   Public Sub ExecuteExcelScript(VBCode As String, ds As DataSet)

      Dim provOptions As New Dictionary(Of String, String)
      provOptions.Add("CompilerVersion", "v4.0")
      Dim vbProvider As New VBCodeProvider(provOptions)
      Dim vbParameter As New CompilerParameters
      Dim compResults As CompilerResults = Nothing

      VBCode = "Imports System" & vbNewLine &
         "Imports System.Data" & vbNewLine &
         "Imports System.Data.SqlClient" & vbNewLine &
         "Imports Microsoft.Office.Interop" & vbNewLine &
         "Public Class GenerateExcel" & vbNewLine &
         "Public Sub GenerateExcelFromScript(ds As DataSet)" & vbNewLine &
         VBCode & vbNewLine &
         "End Sub" & vbNewLine &
         "End Class"

      vbParameter.ReferencedAssemblies.Add("System.dll")
      vbParameter.ReferencedAssemblies.Add("System.Data.dll")
      vbParameter.ReferencedAssemblies.Add("System.Xml.dll")
      vbParameter.ReferencedAssemblies.Add("Microsoft.Office.Interop.Excel.dll")
      vbParameter.GenerateExecutable = False
      vbParameter.GenerateInMemory = True
      vbParameter.OutputAssembly = "ExcelGenerator"
      compResults = vbProvider.CompileAssemblyFromSource(vbParameter, VBCode)

   End Sub

End Class

The code above does not work and returns an error that the file Microsoft.Office.Interop.Excel.dll cannot be found. But it will work if I specify the absolute path of the dll like this:

      vbParameter.ReferencedAssemblies.Add("C:\Program Files (x86)\Microsoft Visual Studio 14.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll")

I cannot deploy the one with the absolute path to our end-users since they might have different versions of Microsoft Office installed and the location of their Excel interop assembly might be different as well. Is there any other way to reference this assembly or obtain the full path of this assembly to be referenced when deployed to another PC?

Solution

Mmmm, how to best explain this...

The .NET Framework interacts with the COM Office applications through (P)IAs = (Primary - optimized by the software manufacturer) Interop Assemblies. These "translate" between the COM *.tlbs and the .NET language.

Starting with version 2003, Microsoft distributed PIAs with Office; starting with version 2007 these are installed automatically. And they are installed into the Windows GAC (Global Assembly Cache). All code that works with the PIAs should work with what's in the GAC.

References to the PIAs can be picked up from the COM tab in "Add References" and these will always refer to the GAC. As many developers were confused by having to add a reference from the COM tab, Visual Studio began distributing a set of PIAs with VS which appear on the .NET tab. These are version-specific (the version of Office that was current when that version of VS was released) and of course the path is static (as you're seeing), but the .NET application will re-map to the GAC automatically, so normally this wouldn't be an issue.

It seems you can refer to the GAC folder location using

%windir%\assembly - prior to .NET 4.0
%windir%\Microsoft.NET\assembly - NET 4.0

If this remains a problem, you can generate a set of IAs using tlbImp.exe and distribute those with your solution. Then you will have a specific path to which you can refer.