Opening an Excel workbook using .NET Interop Assemblies

[mesmith-ut]

I'm trying to use the .NET Office.Interop assemblies (specifically Excel) to open an existing workbook.  I can create an excelApp and open a workbook.  The problem is that the only argument I can get to work for any of the parameters of the Workbooks.Open method (other than the filename, which is a string) is the System.Type.Missing.  And I guess, because of the roots of LabVIEW in C/C++, none of the parameters are really optional as they would be in VBA (although they appear that way on the diagram, if they aren't wired, .NET throws an exception.) The function expects all parameters other than filename to be passed as objects, even the boolean arguments.  So, for instance, I created a System.Boolean control and used the Parse method, thinking that if I passed a "true" string I'd get back a System.Boolean object of type "true"  - nope, it gets converted to a LabVIEW boolean, which obviously won't work (it's not a .NET object).  So, is there any way to create a .NET object of the proper type, cast to object, to represent a bool (or any of the other types in general) - other than writing a .NET DLL that exposes these types as objects?  Thanks for any advice.

[Lycangeek]

LV does attempt to make the necessary conversions when you cross from LV to .NET, but there are cases where it can't. In these cases, you might want to check out the two VIs found in vi.lib\platform\dotnet.llb - To Object.vi and To Variant.vi.

Also, if you could post up a simple example of what you are trying to do, I'll see what else I can think of.

[mesmith-ut]

Brian,

This is exactly what I needed - the To Object.vi lets me convert the types I need to objects.  Interesting to note that the diagram is locked - any hints about how you accomplish this 😉 ?

Also, did I miss something or is this VI ever mentioned in the .NET documentation or examples?

Thanks,

Mark

[Lycangeek]

I don't believe it is mentioned anywhere except my blog and maybe a tech note or something. I am not sure why it isn't documented.

And the VI is locked because no one can have that much knowledge and stay sane.

[mesmith-ut]

Brian,

Next question - why can't I see the properties that should be exposed by the Workbook object returned from the Workbooks.Add method? - see attached VI - sorry for the endless list of questions, but this behavior seems really odd.

[Lycangeek]

The problem is the way the interop assembly is put together. In some cases, the interfaces (such as Workbooks) have the methods and properties defined on them, as well as on the coclass (_Workbooks). In other cases (such as Workbook) the don't. There is a bug in LV 7.x where it gets confused trying to work out the interface relationships in some .NET classes and you have hit that bug (the VI works fine in the version I am working on...but that doesn't help you).

I haven't tried this and won't have time for a bit, but you could try and see if you can convert (using the To More Specific node) to the _Workbook interface.

Message Edited by Lycangeek on 07-18-2005 05:50 PM

[mesmith-ut]

Well, it was worth a try - If I use the To More Specific Class node and cast from  the Workbooks type to the _Workbooks type, the methods and properties are exposed as expected, but then LabVIEW squawks and says -

"Error 1057 occurred at  in excelExample.vi

Possible reason(s):

LabVIEW:  Type mismatch: Object cannot be typecasted to the specified type."

I imagine that means I'm stuck trying to access these methods/properties directly through LabVIEW - or do you have any other ideas?  My fallback will be to wrap these properties in a .NET DLL that I can call from LabVIEW.

Thanks,

Mark

[Lycangeek]

Argh. Well, I did some more playing around and the good news is you should be able to get it working with a much smaller assembly helper than one that wraps all the properties. The To More Specific seems to be hitting that same bug because again it is working fine in my current development version. Try creating a simple wrapper to do the cast for you. Something like

_Workbook ConvertMe(WorkBook arg)

{

   return (_Workbook)arg;

}

Then you can call this assembly to get the other COM interface that has the methods and properties defined on it.

[mesmith-ut]

That works - I just created the converters I needed as static methods in a DLL and now all is OK - I presume that since your version of LV works most of this is expected to be sorted out in the next LabVIEW release?

Thanks for all your help,

Mark

[Lycangeek]

Yes. All of this can be tracked down to the bug in LV's confusion over certain interface inheritances - one that I am glad to say has been fixed 🙂