Culture specific property in Excel via C# using InvokeMember

Question

When we call the Excel object model via COM in C#, it always uses the en-US culture (this is a change that was made some years ago so that addins should work on all machines regardless of regional settings).

I would like to call the Excel object model with a specific culture and the way to do this according to the Excel documentation is using InvokeMember and passing the cultureInfo parameter. https://msdn.microsoft.com/en-us/library/bb157877.aspx

However, whenever I try this with a locale other than en-US, I get a System.Reflection.TargetInvocationException.

So with the example code below, the call will work with the en-US culture, but throw an exception with the fr-CA culture. Note that changing the current culture in the regional settings does not affect whether an exception is thrown or not. Though it will determine if a string is interpreted as a number when using the Value2 property according to the regional settings.

I based the code off of this example, which seemed to be working when it was written: https://social.msdn.microsoft.com/Forums/office/en-US/2aee0a8a-aaff-48a8-9364-edf1e3fbb9b4/setting-rangevalue2-behavior-changed-between-net-20-and-net-40-for-international-versions-of?forum=exceldev

Does anyone know what changes I can make to call the Range.Value2 with a specific culture other than en-US?

    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        try
        {
            var excelApp = Globals.ThisAddIn.Application;
            var workbook = excelApp.Workbooks.Add();
            var worksheet = (Excel.Worksheet)workbook.Worksheets[1];
            var rangeA1 = worksheet.Range["A1"];
            var rangeB1 = worksheet.Range["B1"];
            string num1English = "1.1";
            string num1French = "1,1";

            rangeA1.GetType().InvokeMember("Value2", BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.Public, null,
                                            rangeA1, new object[] { num1English }, new CultureInfo("en-US"));

            rangeB1.GetType().InvokeMember("Value2", BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.Public, null,
                                            rangeB1, new object[] { num1French }, new CultureInfo("fr-CA"));
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString() + "\n\n" + ex.StackTrace);
        }
    }

It looks to me like you're not passing what you want to write to the ranges as an object array? num1English and num1French are string objects... InvokeMember is pretty picky about how things are sent to it. Before using a Help method, like you have, it would be better to test directly... Also, what do you expect the method to return? A return value from this doesn't make a lot of sense... — Cindy Meister, May 12 '16 at 17:55
@CindyMeister thanks for your reply. I agree that the helper method confused things. I have updated the sample code so that it calls directly. It still exhibits the same behavior, throwing the exception with the non-english CultureInfo object. — tjsmith, May 12 '16 at 20:19

score 0 · Answer 1 · answered Sep 01 '16 at 11:28

0

Find an example in HowTo: Fix “Old format or invalid type library” error (0x80028018). You can use any VB.NET to C# code converter available online.

answered Sep 01 '16 at 11:28

Andrei Smolin - Add-in Express

444
2
8

score 0 · Answer 2 · answered Jul 30 '20 at 07:20

0

You misread the documentation. It says to pas the locale id, not the CultureInfo. This is also logical, Excel is not written using the .NET Framework and the COM technology existed far earlier and has a wider spectrum of usages than .NET itself, so that parameter should work across different programming languages. What you should pass is the LCID property of the CultureInfo instance.

answered Jul 30 '20 at 07:20

Tommaso Ercole

443
5
7

1

How do you pass a Locale ID instead of CultureInfo to the InvokeMember method? It accepts the last parameter of type CultureInfo. Could you post an example of the C# method invocation passing a Locale ID, please? – Jonas Apr 23 '21 at 10:38

Culture specific property in Excel via C# using InvokeMember

2 Answers2