Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
issue with RefersTo in non-EN languages
Sorry for the long post, but in brief:
Setting RefersTo property on a Microsoft.Office.Interop.Excel.NamedRange in PT-BR yields different/unexpected results when the same property is subsequently retrieved (as compared to creating the NamedRange using names.Add()). Using Portuguese/Brazil o/s, if I use names.Add() to create a named range for "='Plan1'!$B$3:$D$5", when I later retrieve the RefersTo property, I get =Plan1!$B$3:$D$5, good so far. But, if I *set* the RefersTo property on the named range to "='Plan1'!$B$3:$D$5", then when I later retrieve that same property I get =Plan1!L3C2:L5C4 -- this is unexpected. Yes, I saw this post: http://msdn.microsoft.com/newsgroups...d-aeee3909775b but it does not seem to be the same issue. I have the same issue running German, except that the returned RefersTo is something like: =Tabelle1!Z3S2:Z5S4 Details: Development: Win XP SP2 (EN-US), VS 2005 SP1 + VSTO 2005 SE, Excel 2003 SP2 Runtime: Win Server 2003 SP1 (PT-BR), VSTO 2005 SE, Excel 2003 (11.5612.5606) See code snippet below. In EN-US, "output" values when the properties are retrieved are consistent and expected. In PT-BR (Portuguese/Brazil), there are inconsistencies, particularly in Test1 and Test2. Take note that in Test1, the RefersTo property after using names.Add() is =Plan1!$B$3: $D$5 but after using the namedRange.RefersTo setter, it is =Plan1!L3C2:L5C4. Empirically, it looks like I can probably change over to using get_AddressLocal (A1) + RefersToLocal to get a consistent result, but the behavior in Test1 seems like a bug -- unless I am missing something - which could definitely be the case. I would prefer to use get_Address() since that *should* be locale-independent. Is there some way I can do that and get back predictable values from namedRange.RefersTo on different locales when using the two approaches of setting RefersTo? // In the code snippet below there are 4 tests. Each test creates a named range using names.Add and dumps out its properties // It then explicitly sets the RefersToXXX property and dumps out the properties again. // Test1 uses get_Address (A1 style) + .RefersTo // Test2 uses get_Address (R1C1 style) + .RefersToR1C1 // Test3 uses get_AddressLocal (A1 style) + .RefersToLocal // Test4 uses get_AddressLocal (R1C1 style) + .RefersToR1C1Local using Excel = Microsoft.Office.Interop.Excel; // . . . private void ThisWorkbook_Startup (object sender, System.EventArgs e) { Excel.Workbook workbook = this.InnerObject; Excel.Worksheet worksheet = workbook.ActiveSheet as Excel.Worksheet; Excel.Range testRange = worksheet.get_Range ("$B$3:$D$5", Type.Missing); Excel.Names names = workbook.Names; string refersTo, addr, name, msg; Excel.Name namedRange; // TEST 1 name = "test1_A1"; addr = testRange.get_Address (true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xl A1, false, Type.Missing); refersTo = "='" + worksheet.Name + "'!" + addr; // just add the name namedRange = names.Add (name, //Name refersTo, //RefersTo true, //Visible Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro, shortcut, category, NameLocal Type.Missing, // RefersToLocal Type.Missing, // CategoryLocal Type.Missing, // RefersToR1C1 Type.Missing); // RefersToR1C1Local msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Add"); // now get the range via names.item namedRange = names.Item (name, Type.Missing, Type.Missing); // and set RefersTo directly namedRange.RefersTo = refersTo; msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)"); // TEST 2 name = "test2_R1C1"; addr = testRange.get_Address (true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xl R1C1, false, Type.Missing); refersTo = "='" + worksheet.Name + "'!" + addr; // just add the name namedRange = names.Add (name, //Name Type.Missing, //RefersTo true, //Visible Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro, shortcut, category, NameLocal Type.Missing, // RefersToLocal Type.Missing, // CategoryLocal refersTo, // RefersToR1C1 Type.Missing); // RefersToR1C1Local msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Add"); // now get the range via names.item namedRange = names.Item (name, Type.Missing, Type.Missing); // and set RefersTo directly namedRange.RefersToR1C1 = refersTo; msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)"); // TEST 3 name = "test3_A1Local"; addr = testRange.get_AddressLocal (true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xl A1, false, Type.Missing); refersTo = "='" + worksheet.Name + "'!" + addr; // just add the name namedRange = names.Add (name, //Name Type.Missing, //RefersTo true, //Visible Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro, shortcut, category, NameLocal refersTo, // RefersToLocal Type.Missing, // CategoryLocal Type.Missing, // RefersToR1C1 Type.Missing); // RefersToR1C1Local msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Add"); // now get the range via names.item namedRange = names.Item (name, Type.Missing, Type.Missing); // and set RefersTo directly namedRange.RefersToLocal = refersTo; msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)"); // TEST 4 name = "test4_R1C1Local"; addr = testRange.get_AddressLocal (true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xl R1C1, false, Type.Missing); refersTo = "='" + worksheet.Name + "'!" + addr; // just add the name namedRange = names.Add (name, //Name Type.Missing, //RefersTo true, //Visible Type.Missing, Type.Missing, Type.Missing, Type.Missing, // macro, shortcut, category, NameLocal Type.Missing, // RefersToLocal Type.Missing, // CategoryLocal Type.Missing, // RefersToR1C1 refersTo); // RefersToR1C1Local msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Add"); // now get the range via names.item namedRange = names.Item (name, Type.Missing, Type.Missing); // and set RefersTo directly namedRange.RefersToR1C1Local = refersTo; msg = "Input: refersTo: " + refersTo + "\nOutput:" + "\nRefersTo: " + namedRange.RefersTo.ToString () + "\nRefersToLocal: " + namedRange.RefersToLocal.ToString () + "\nRefersToR1C1: " + namedRange.RefersToR1C1.ToString () + "\nRefersToR1C1Local: " + namedRange.RefersToR1C1Local.ToString (); MessageBox.Show (msg, name + " after names.Item + namedRange.RefersTo (set)"); Here is the output: // OUTPUT // TEST 1 test1_A1 after names.Add Input: RefersTo: ='Plan1'!$B$3:$D$5 Output: RefersTo: =Plan1!$B$3:$D$5 RefersToLocal: =Plan1!$B$3:$D$5 RefersToR1C1: =Plan1!R3C2:R5C4 RefersToR1C1Local: =Plan1!L3C2:L5C4 test1_A1 after names.Item + namedRange.RefersTo (set) Input: RefersTo: ='Plan1'!$B$3:$D$5 Output: RefersTo: =Plan1!L3C2:L5C4 RefersToLocal: =Plan1!'L3C2':'L5C4' RefersToR1C1: =Plan1!L3C2:L5C4 RefersToR1C1Local: =Plan1!'L3C2':'L5C4' // TEST 2 test2_R1C1 after names.Add Input: RefersTo: ='Plan1'!R3C2:R5C4 Output: RefersTo: =Plan1!'R3C2':'R5C4' RefersToLocal: =Plan1!R3C2:R5C4 RefersToR1C1: =Plan1!'R3C2':'R5C4' RefersToR1C1Local: =Plan1!R3C2:R5C4 test2_R1C1 after names.Item + namedRange.RefersTo (set) Input: RefersTo: ='Plan1'!R3C2:R5C4 Output: RefersTo: =Plan1!$B$3:$D$5 RefersToLocal: =Plan1!$B$3:$D$5 RefersToR1C1: =Plan1!R3C2:R5C4 RefersToR1C1Local: =Plan1!L3C2:L5C4 // TEST 3 test3_A1Local after names.Add Input: RefersTo: ='Plan1'!$B$3:$D$5 Output: RefersTo: =Plan1!$B$3:$D$5 RefersToLocal: =Plan1!$B$3:$D$5 RefersToR1C1: =Plan1!R3C2:R5C4 RefersToR1C1Local: =Plan1!L3C2:L5C4 test3_A1Local after names.Item + namedRange.RefersTo (set) Input: RefersTo: ='Plan1'!$B$3:$D$5 Output: RefersTo: =Plan1!$B$3:$D$5 RefersToLocal: =Plan1!$B$3:$D$5 RefersToR1C1: =Plan1!R3C2:R5C4 RefersToR1C1Local: =Plan1!L3C2:L5C4 // TEST 4 test4_R1C1Local after names.Add Input: RefersTo: ='Plan1'!L3C2:L5C4 Output: RefersTo: =Plan1!$B$3:$D$5 RefersToLocal: =Plan1!$B$3:$D$5 RefersToR1C1: =Plan1!R3C2:R5C4 RefersToR1C1Local: =Plan1!L3C2:L5C4 test4_R1C1Local after names.Item + namedRange.RefersTo (set) Input: RefersTo: ='Plan1'!R3C2:R5C4 Output: Input: RefersTo: ='Plan1'!L3C2:L5C4 Output: RefersTo: =Plan1!$B$3:$D$5 RefersToLocal: =Plan1!$B$3:$D$5 RefersToR1C1: =Plan1!R3C2:R5C4 RefersToR1C1Local: =Plan1!L3C2:L5C4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Windows "Region & Languages" ComboBox Issue | Excel Programming | |||
refersTo property | Excel Programming | |||
Help building string for Names.Add RefersTo, pls? | Excel Programming | |||
Wierd named range RefersTo value | Excel Programming | |||
Wierd named range RefersTo value | Excel Programming |