The correct separator to use in Excel get_Range in VSTO

c#, cultureinfo, excel, vsto

Solution

Not the cleanest approach, but this workaround helped me:

private static string GetRangeSeparator(Excel.Worksheet sheet)
{
     Excel.Application app = sheet.Application;
     string sRng = app.Union(sheet.get_Range("A1"), sheet.get_Range("A3")).AddressLocal;
     sRng = sRng.Replace("$", string.Empty);
     string sSep = sRng.Substring(sRng.IndexOf("A3") - 1, 1);
     return sSep;
}

Hope it'll help someone.

Problem

In a VSTO project for Excel written in C#, I need to get the Range object from a string list of cells. Here is a simplified version of the problem: ``` string strRange = "A1:A2,A5"; Excel.Range r = sheet.get_Range(strRange); ``` However since the list separator can be different from the comma in different culture settings I'm actually using this: ``` listSep = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator; string strRange = "A1:A2" + listSep + "A5"; Excel.Range r = sheet.get_Range(strRange); ``` My problem is when the user changes "Decimal Separator" in Excel Options > Advanced (the Application.DecimalSeparator) to match the ListSeparator, this won't work. What is the correct way to call get_Range with a string specifying the Range? EDIT: Slight modification to add information of my comment below.

Original source

Related problems