none
Excel Interop: Structured Reference in Multilingual Environment using c# not working RRS feed

  • Question

  • I asked a "multilingual" question already here: Link   and here:  Link2

    In "Link2", the problem was code like this: 

    myRange = workSheet.Range["MyTable[[#Headers],[Colx]]"];  // c# - code

    This problem was resolved: I substituted the  , (Comma) by  ;  (Semikolon). This works for "en-US" display-language.

    Now I switch to display-language "de-DE" in Excel-Options. 

    UPDATED/SIMPLIFIED Code is here:

    using Exc = Microsoft.Office.Interop.Excel;
     
    namespace ExcelLocalization {
        class Program {
            static void Main( string[] args ) {
                Exc.Application _xclApp = new Exc.Application();
                Exc.Workbook _xclWb = _xclApp.Workbooks.Add();
                _xclApp.Visible = true;
                Exc.Worksheet _sh = _xclWb.Sheets[1];
     
                // add some data and create a ListObject/Table with header
                _sh.get_Range("B5").Value = "MyHeader";
                _sh.get_Range("B6").Value = "a";
                _sh.get_Range("B7").Value = "b";
                _sh.ListObjects.AddEx(Exc.XlListObjectSourceType.xlSrcRange, _sh.get_Range("B5:B7"), null, Exc.XlYesNoGuess.xlYes).Name = "MyTable";
     
                // Crash here:     access the table by a structured reference
                _sh.Range["MyTable[[#Data];[MyHeader]]"].Interior.Color = Exc.XlRgbColor.rgbYellowGreen;
            }
        }
    }

    Code crashes at last line with Exception: 0x800A03EC  I tried a lot but I have no further idea. If display-language is "en-US", it works perfectly.

    Do you, dear reader, have any idea?








    • Edited by Somigur Friday, October 19, 2018 9:29 AM
    Thursday, October 18, 2018 1:20 PM

Answers

  • Thanks, Lina, you gave me the decisive hint: After month I have a 100% solution (at least for now) for two UI-Languages: English and German.

    The solution is: Translate "Tags", dependent on UI-Language of Excel 

    Here is my Setup/Environment

    1. Windows system-language    = english
    2. Windows-location                 = germany
    3. Windows diaply-languabe      = english/german           (arbitrarily selected)
    4. Office-display language         = english/german           (arbitrarily selected)

    using Exc = Microsoft.Office.Interop.Excel;
     
    namespace ExcelLocalization {
        class Program {
            static void Main( string[] args ) {
                Exc.Application _xclApp = new Exc.Application();
                Exc.Workbook _xclWb = _xclApp.Workbooks.Add();
                _xclApp.Visible = true;
                Exc.Worksheet _sh = _xclWb.Sheets[1];
     
                // add some data and create a ListObject/Table with header
                _sh.get_Range("B5").Value = "Col1";  _sh.get_Range("B6").Value = "a";  _sh.get_Range("B7").Value = "b";
                _sh.get_Range("C5").Value = "Col2";  _sh.get_Range("C6").Value = "a";  _sh.get_Range("C7").Value = "b";
                _sh.ListObjects.AddEx(Exc.XlListObjectSourceType.xlSrcRange, _sh.get_Range("B5:C7"), null, Exc.XlYesNoGuess.xlYes).Name = "MyTable";
     
     
                //access the table by a structured reference
                // Crash here:    with German UI-Language
                //_sh.Range["MyTable[[#Data];[MyHeader]]"].Interior.Color = Exc.XlRgbColor.rgbYellowGreen;
     
                // for Testing, set colors to different areas
                // Solution 1:  Substitute  "Data"  by  german  "Daten", depending of UI-Language of office-tools
                int curLcdId = _xclApp.LanguageSettings.get_LanguageID(Microsoft.Office.Core.MsoAppLanguageID.msoLanguageIDUI);
                if (1031 == curLcdId) {
                    _sh.Range["MyTable[#Daten]"].Interior.Color = Exc.XlRgbColor.rgbLightSlateGray;
                    _sh.Range["MyTable[[#Daten];[Col1]]"].Interior.Color = Exc.XlRgbColor.rgbGainsboro;
                    _sh.Range["MyTable[[#Daten];[Col2]]"].Interior.Color = Exc.XlRgbColor.rgbAquamarine;
                    _sh.Range["MyTable[#Kopfzeilen]"].Interior.Color = Exc.XlRgbColor.rgbBlueViolet;
                }
                else if (1033 == curLcdId) {
                    _sh.Range["MyTable[#Data]"].Interior.Color = Exc.XlRgbColor.rgbLightSlateGray;
                    _sh.Range["MyTable[[#Data];[Col1]]"].Interior.Color = Exc.XlRgbColor.rgbGainsboro;
                    _sh.Range["MyTable[[#Data];[Col2]]"].Interior.Color = Exc.XlRgbColor.rgbAquamarine;
                    _sh.Range["MyTable[#Headers]"].Interior.Color = Exc.XlRgbColor.rgbBlueViolet;
                }
     
     
                // Solution 2: even simpler syntax. "Simple-Selection" of Columns-DataBody. Does not use Tag '#Data'
                _sh.Range["MyTable[Col1]"].Interior.Color = Exc.XlRgbColor.rgbGold;
                _sh.Range["MyTable[Col2]"].Interior.Color = Exc.XlRgbColor.rgbFireBrick;
            }
        }
    }

    Here some additional notes:

    • The solution uses the same concept as here: FollowMe

    • Even if the solution works for my current szenario. The type of programming is ugly. I feel, there must be another way to support multiple languages.

    • During my investigations I found the following code "a million of times" for resolving culture-problems
    CultureInfo cultureOld = System.Threading.Thread.CurrentThread.CurrentCulture;
    CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
    ..... other code
    CurrentThread.CurrentCulture = cultureOld;

          I tried such code endlessly, without any effect in my case. I am wondering.



    • Edited by Somigur Tuesday, October 23, 2018 8:43 AM
    • Marked as answer by Somigur Tuesday, October 23, 2018 9:02 AM
    Tuesday, October 23, 2018 8:40 AM

All replies

  • Hi Somigur,

    >>display-language is switched e.g. to "de-DE". My code crashes with Exception: 0x800A03EC.

    This is a common but poorly documented Excel COM Error. Whether it is recorded as "NAME_NOT_FOUND", and if so, this means that the COM layer of Excel is disabled and the COM property or method name is not found.

    Have you tried changing the data parameter to German?

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 19, 2018 5:48 AM
  • Hi Somigur,

    Thanks for your asking. Please remember to mark the replies(Include your solution) as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 22, 2018 5:25 AM
  • Thanks, Lina, you gave me the decisive hint: After month I have a 100% solution (at least for now) for two UI-Languages: English and German.

    The solution is: Translate "Tags", dependent on UI-Language of Excel 

    Here is my Setup/Environment

    1. Windows system-language    = english
    2. Windows-location                 = germany
    3. Windows diaply-languabe      = english/german           (arbitrarily selected)
    4. Office-display language         = english/german           (arbitrarily selected)

    using Exc = Microsoft.Office.Interop.Excel;
     
    namespace ExcelLocalization {
        class Program {
            static void Main( string[] args ) {
                Exc.Application _xclApp = new Exc.Application();
                Exc.Workbook _xclWb = _xclApp.Workbooks.Add();
                _xclApp.Visible = true;
                Exc.Worksheet _sh = _xclWb.Sheets[1];
     
                // add some data and create a ListObject/Table with header
                _sh.get_Range("B5").Value = "Col1";  _sh.get_Range("B6").Value = "a";  _sh.get_Range("B7").Value = "b";
                _sh.get_Range("C5").Value = "Col2";  _sh.get_Range("C6").Value = "a";  _sh.get_Range("C7").Value = "b";
                _sh.ListObjects.AddEx(Exc.XlListObjectSourceType.xlSrcRange, _sh.get_Range("B5:C7"), null, Exc.XlYesNoGuess.xlYes).Name = "MyTable";
     
     
                //access the table by a structured reference
                // Crash here:    with German UI-Language
                //_sh.Range["MyTable[[#Data];[MyHeader]]"].Interior.Color = Exc.XlRgbColor.rgbYellowGreen;
     
                // for Testing, set colors to different areas
                // Solution 1:  Substitute  "Data"  by  german  "Daten", depending of UI-Language of office-tools
                int curLcdId = _xclApp.LanguageSettings.get_LanguageID(Microsoft.Office.Core.MsoAppLanguageID.msoLanguageIDUI);
                if (1031 == curLcdId) {
                    _sh.Range["MyTable[#Daten]"].Interior.Color = Exc.XlRgbColor.rgbLightSlateGray;
                    _sh.Range["MyTable[[#Daten];[Col1]]"].Interior.Color = Exc.XlRgbColor.rgbGainsboro;
                    _sh.Range["MyTable[[#Daten];[Col2]]"].Interior.Color = Exc.XlRgbColor.rgbAquamarine;
                    _sh.Range["MyTable[#Kopfzeilen]"].Interior.Color = Exc.XlRgbColor.rgbBlueViolet;
                }
                else if (1033 == curLcdId) {
                    _sh.Range["MyTable[#Data]"].Interior.Color = Exc.XlRgbColor.rgbLightSlateGray;
                    _sh.Range["MyTable[[#Data];[Col1]]"].Interior.Color = Exc.XlRgbColor.rgbGainsboro;
                    _sh.Range["MyTable[[#Data];[Col2]]"].Interior.Color = Exc.XlRgbColor.rgbAquamarine;
                    _sh.Range["MyTable[#Headers]"].Interior.Color = Exc.XlRgbColor.rgbBlueViolet;
                }
     
     
                // Solution 2: even simpler syntax. "Simple-Selection" of Columns-DataBody. Does not use Tag '#Data'
                _sh.Range["MyTable[Col1]"].Interior.Color = Exc.XlRgbColor.rgbGold;
                _sh.Range["MyTable[Col2]"].Interior.Color = Exc.XlRgbColor.rgbFireBrick;
            }
        }
    }

    Here some additional notes:

    • The solution uses the same concept as here: FollowMe

    • Even if the solution works for my current szenario. The type of programming is ugly. I feel, there must be another way to support multiple languages.

    • During my investigations I found the following code "a million of times" for resolving culture-problems
    CultureInfo cultureOld = System.Threading.Thread.CurrentThread.CurrentCulture;
    CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
    ..... other code
    CurrentThread.CurrentCulture = cultureOld;

          I tried such code endlessly, without any effect in my case. I am wondering.



    • Edited by Somigur Tuesday, October 23, 2018 8:43 AM
    • Marked as answer by Somigur Tuesday, October 23, 2018 9:02 AM
    Tuesday, October 23, 2018 8:40 AM
  • Hi Somigur,

    You are welcome. You can mark it as answer and please help us close the thread.

    If possible, you can collect all the different languages you need to use and then call the custom method. Of course, this is my suggestion.

    Have a good day!

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.



    • Edited by Lina-MSFT Tuesday, October 23, 2018 8:53 AM
    Tuesday, October 23, 2018 8:52 AM