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

Question

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?

score 0 · Answer 1 · answered Apr 29 '16 at 11:15

0

Try using this in your imports "Imports Excel = Microsoft.Office.Interop.Excel", I have used it in this way for my excel doc creation.

answered Apr 29 '16 at 11:15

Leprechaun

349
2
16

Thank you for your reply sir. But what I mean is that when you add the Microsoft.Office.Interop.Excel.dll as one of the referenced assemblies. I have to put the absolute path (or the whole file path) of the .dll in order for it to work. If absolute path is needed to reference the interop assembly then I have to set the file path of the assembly for each computer that my app will be deployed since there would be different versions of Microsoft Office installed for every client pc that will result to different paths of the interop assembly. – cod3n00b Apr 29 '16 at 14:31

score 0 · Accepted Answer · edited May 23 '17 at 11:45

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

See also Where is the .NET Framework Global Assembly Cache?, and especially (Physical)(Installed) path of DLL installed to the GAC

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.

Your answer solves my problem and also thank you for giving concrete information especially with regards to the difference of IAs that can be found in GAC and in the VS directories. Back to my application I'll just refer to the GAC and maybe add a method that will search for the Excel Interop due to the path might still be different in GAC since it may have different versions of Microsoft Office installed per client pc. — cod3n00b, Apr 30 '16 at 14:55

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

2 Answers2