Missing Added Reference When Using CodeDom Compiler

Question

I've found similar questions to this but not managed to find something which specifically addresses my problem yet.

I have some code which edits an Excel workbook via System.CodeDom.Compiler. This is important as I want to be able to 'plug-in' different editing instructions for different workbooks later on.

The reference to Microsoft.Office.Interop.Excel.Dll was added to my project using References>Add... in visual studio and I have added the reference for the excel dll for the CodeDom compiler using .ReferencedAssemblies.Add as follows:

CSharpCodeProvider provider = new CSharpCodeProvider(providerOptions);

CompilerParameters compilerParams = new CompilerParameters();
compilerParams.GenerateInMemory = true;
compilerParams.GenerateExecutable = false;
compilerParams.ReferencedAssemblies.Add("System.Windows.Forms.Dll");
compilerParams.ReferencedAssemblies.Add("Microsoft.Office.Interop.Excel.Dll");

But unfortunately, this error occurs:

{error CS0006: Metadata file 'Microsoft.Office.Interop.Excel.Dll' could not be found}

Is there any easy way to tell the CodeDom compiler to find this dll?

I have tried compilerParams.ReferencedAssemblies.Add(typeof(Microsoft.Office.Interop.Excel.Application).Assembly.Location); as suggested elsewhere, but this only points the compiled program exe, and not the required dll.

Thanks, Joe.

Answer 1

This might not be the answer, but its too much for a comment and could help as a "workaround".

I managed to load System.Data.dll which was NOT part of the hosting-assembly. I placed it in the executing "bin" folder manually, just for testing. My mistake was pretty obvious I used:

String pathToDll = System.Reflection.Assembly.GetExecutingAssembly().CodeBase;

to get the base directory of that dll. But I got an uri (file:///C:/...). Once parsed to "C:\..." everything went well.

Since your dll cannot be found you could edit the Properties of that Reference and set "CopyLocal = true", so that the dll will end up in the same path as the .exe .. After that you should be able to load it:

String pathAndFile = System.Reflection.Assembly.GetExecutingAssembly().CodeBase;    
pathAndFile = Path.GetDirectoryName(pathAndFile);
Uri uri = new Uri(pathAndFile);
pathAndFile = uri.LocalPath + "\\" + "Microsoft.Office.Interop.Excel.Dll";
compilerParams.ReferencedAssemblies.Add(pathAndFile);

Also, take a look at this little piece of code, it should load everything from the hosting assembly. (Warning: You cannot load a single assembly twice in the same AppDomain)

var assemblies = AppDomain.CurrentDomain
                       .GetAssemblies()
                       .Where(a => !a.IsDynamic)
                       .Where(a => !parameters.ReferencedAssemblies.Contains(a.Location))
                       .Select(a => a.Location);

parameters.ReferencedAssemblies.AddRange(assemblies.ToArray());

And as far as I have read, these two tutorials are a bit old (depricated code) but helped me understand thing a little more:

https://west-wind.com/presentations/DynamicCode/DynamicCode.htm https://weblog.west-wind.com/posts/2016/Dec/12/Loading-NET-Assemblies-out-of-Seperate-Folders