Retrieving cell values with the Excel Interop

Question

I'm trying to retrieve a cell value from a spreadsheet using the Excel interop, but the following code I'm using is generating a compiler error:

One or more types required to compile a dynamic expression cannot be found. Are you missing a reference?

Range currentFind = sheet.Cells.Find("some string", Type.Missing,
                    XlFindLookIn.xlValues, XlLookAt.xlPart, 
                    XlSearchOrder.xlByRows, XlSearchDirection.xlNext,
                    false, Type.Missing, Type.Missing);

if (currentFind[1,1].Value != null)
{
    string CellValue = currentFind[1,1].Value.ToString();
}

Any idea what's going on here? I'm guessing something might need to be cast, but I'm not sure what. I've tried casting currentFind[1,1].Value, but that doesn't seem to work.

EDIT:

I've found a workaround, set Embed Interop Types to False on the reference, but I would still like to know what is going on here and how to handle this situation.

Did either of the supplied answers answer your question or did you get to the bottom of it in a different way? — Andy Brown, Jun 13 '13 at 10:26
@Andy- Sorry, I never realized someone had answered this since I had figured it out. Thanks for the good info! — Seth Moore, Jun 24 '13 at 18:55

score 4 · Accepted Answer · edited May 23 '17 at 12:14

Have you added a reference to Microsoft.CSharp as well as Microsoft.Office.Interop.Excel? That fixed this issue for me in the past - the exception is saying that type information required to resolve the dynamic type of the Value property is missing. The exception usually tries to suggest the missing references as well.

What is happening with Embed Interop Types?

.NET (C#) 4.0 introduced dynamic and the ability to treat COM host return object values as the dynamic type. This is supposed to make our lives easier when writing code as we don't have to explicitly cast return objects to the type we want when writing code. This happens if "Embed Interop Types" is set to true on your assembly; this is also the default value for references you add in VS.

Essentially VS is embedding the subset of the PIA for the types that you use in your assembly, at compile time (see the answer to "what is the advantage of .net4's new no pia feature [deploying PIA's]" for why this is good). But, it needs access to all the PIAs in the reference tree to do this.

dynamic allows late binding. So I'm guessing in your case Value has become a dynamic type which will be resolved at runtime, and that is going wrong as it can't get to the correct assembly and PIA reference. If you turn "Embed Interop Types" to false then Value becomes an object type and this prevents the issue (but you now need to cast to the correct type at some point).

Why embed PIA information?

It can make it easier to deploy and support multiple versions of Office (as long as you don't use unsupported features in earlier/later versions than you tested). This is not easy, though; lots can go wrong.

MSDN - Walkthrough: Office Programming (C# and Visual Basic) mentions this, but doesn't make big deal of it other than to say it is a good thing.

As an example: if I fire up a project in VS2010 and reference the VSTO Excel 2010 interop located at

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll

then I do not get this problem with Embed Interop Types set to true as it automatically adds

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\Microsoft.CSharp.dll

as a reference as well. If I then remove the Microsoft.CSharp reference, I get the compiler error, but it states the following error, which was how I found my solution originally:

One or more types required to compile a dynamic expression cannot be found. Are you missing references to Microsoft.CSharp.dll and System.Core.dll?

score 0 · Answer 2 · answered May 14 '13 at 10:02

It seems like your need to add some reference to your project. Not sure what other code you have but if this is it you need to add interop assemblies to your project. Check if you editor marks any lines with errors and check the types of the objects you use in that line.

On a side note I see a potential problem with the following line:

if (currentFind[1,1].Value != null)

If the value is not found then the currentFind is null and you are trying to access null like an array. Try to check if currentFind is not null:

if (currentFind != null)

Yeah, I have the interop assemblies in the project already. You're right about that potential problem, but this isn't the actual code I'm running, just a simpler test case. — Seth Moore, May 14 '13 at 20:12

Retrieving cell values with the Excel Interop

2 Answers2