Skip to content

.Net Interop: Using Enumerations

January 18, 2013

If you are using .Net Interop you may came across waldos blog post about enumerations. I already successfully used this nice trick some times.
Then lately i had to deal with another excel import, so i thought why not using interop instead of automation.This would have been a pleasure if the database was a NAV2013, because MS provided assemblies to do the “hard” work, you can have a look at it when you go to the redesigned ExcelBuffer Table. But since it was a 2009 R2 Database i started from scratch.

So i started using the ‘Microsoft.Office.Interop.Excel’ assembly and tried it that way. That all worked very well since the code is almost identical to what we’re used to with com automation.

But then i wanted to open an existing workbook with the method which has a parameter of type “Microsoft.Office.Interop.Excel.XlPlatform” which is en Enum. So far so good, lets do it with waldos trick to have some nice looking understandable code (instead of some integer values…).

BUT WAIT!

Oh dear… there is no instance of this Type available like in the example… hmm… if i pass an not instanciated .net interop variable to the EnumToInt function it will crash saying it’s not instanciated…
Thats bad, should i use an integer and let it be unreadable?
Of course not! There is another solution to this which even works when you don’t have an instance of the Enum you want to Parse. Here comes the code


// xlPlatformFullName is a TextConstant with this content:
// Microsoft.Office.Interop.Excel.XlPlatform, Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
//
xlWorkBook := xlApp.Workbooks.Open(Filename, 0,TRUE,5,'','',TRUE,EnumToInt(xlPlatformFullName, 'xlWindows'),'\t',FALSE,FALSE,0,TRUE,1,0);

EnumToInt(_FullyQualifiedName : Text[1024];_EnumValue : Text[120]) : Integer
  EXIT(Convert.ToInt32(
    Enum.Parse(
      Type.GetType(_FullyQualifiedName,TRUE,TRUE),
        _EnumValue)
    )
  );

Variables

 Name    DataType    Subtype    Length
 Type    DotNet    'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Type
 Enum    DotNet    'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum
 Convert    DotNet    'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert
 

You use the EnumToInt Function by providing the Fully Qualified Name of the Enum Type you want to assign and its value.
If you don’t know the fully qualified name you can just try to create a variable of that type. In the Subtype column in NAV you will find all the information you need. It’s just in the wrong order.

Lets have a look at xlPlatform for as an example. If you declare it as a NAV variable you get this as subtype:

'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.XlPlatform

the result should look like this:

Microsoft.Office.Interop.Excel.XlPlatform, Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c

Basicly you just need to delete the high comma [‘] chars and put the type name which is in the variable decleration at the end (“Microsoft.Office.Interop.Excel.XlPlatform”), to the front and seperate it with a comma. In the example the Version and Culture are switched too, but guess that one is not absolutly necessary. But to be sure, keep the Order:[Full Type Name With Namespaces], [Assembly Name without extension], [Version], [Culture], [PublicKeyToken]

Advertisements

From → C# .NET, Dynamics NAV

7 Comments
  1. Reka permalink

    Hi,
    I tried to import the excel value into NAV with your code. The following is the error message I’m getting

    A call to System._ComObject.Open failed with this message: The type of one or more arguments does not match the method’s parameter type.

    Can you please let me know what needs to be provided for the XlplatformFullName.

    Thanks,
    Reka.S

  2. As you can see in the code comment:
    xlPlatformFullName is a TextConstant with this content:
    Microsoft.Office.Interop.Excel.XlPlatform, Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c

  3. Reka permalink

    I tried with the same and get the same error. Do you have any idea on this error.

  4. Reka permalink

    Here is my code

    // xlPlatformFullName is a TextConstant with this content:
    // Microsoft.Office.Interop.Excel.XlPlatform, Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
    //

    XlplatformFullName:= ‘Microsoft.Office.Interop.Excel.XlPlatform, Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’;
    XLApp:=XLApp.ApplicationClass();
    MESSAGE(WorkBookPath);
    XlBook:=XLApp.Workbooks.Open(WorkBookPath,0,TRUE,5,”,”,TRUE,EnumToInt(XlplatformFullName, ‘xlWindows’),’\t’,FALSE,FALSE,0,TRUE,1,0);

    XlSheet:=XlBook.Worksheets.Item(1);

  5. Reka permalink

    Hi Dev,
    I Somehow managed to Open the excel with XLhelper.callopen. But now i get the error message in xlsheet.range(‘A2’).Text. “A dontnet variable has not been instantiated. Attempting to call Microsoft.Office.Excel.Interop.IRange.Text”. Any idea on this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s