Re: FormulaR1C1 separator depends on locale

[Govert van Drimmelen]

Hi Dmitry,

I have three plans you can try:

(1) Set the xlIntl (0x2000) bit in the function or command
enumeration, i.e. try something like
XlCall.Excel(XlCall.xlcFormulaFill + XlCall.xlIntl, new object[]
{textNew, theRef});
This is supposed to allow you to use English function names when
setting formulae (like =SUM(A1:A10)) and the formula should appear in
the localised language on the sheet.

(2) You can read the current delimiters with a call to GET.WORKSPACE.
Type_num 37 returns an array with regional information ans settings,
then the 5th entry gives you the list separator.
Your code might look like this:

object[,] workspaceSettings =
(object[,])XlCall.Excel(XlCall.xlfGetWorkspace, 37);
string listSeparator = (string)workspaceSettings[0, 4];

Full documentation for the macro functions is in the old Excel macro
help - pointers to a .chm version and a searchable .pdf are here:
https://exceldna.codeplex.com/wikipage?title=Excel%20C%20API

(3) You can use the COM Automation interface, using
ExcelDnaUtil.Application to get hold of the root application, then
make the call as you would from VBA. You might still need a call to
set the thread locale before this (and reset after). The locale switch
might be something like this:

System.Globalization.CultureInfo oldCI;
//get the old CurrenCulture and set the new, en-US
void SetNewCurrentCulture()
{
oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new
System.Globalization.CultureInfo("en-US");
}

//reset Current Culture back to the originale
void ResetCurrentCulture()
{
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}

The locale switch can interfere with other add-ins, and if you're
worried about this you can make the call to the COM automation
interface via reflection (.Invoke(...)) which will allow you to set
the local for that call. (Or does it just set the reflection method
name binding? I don't know.)


So there are some ideas to experiment with. Please write back with
what you find.

Regards,
Govert


* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/

* * * * * * * * * * * * * * * * * * * * * * * * * * * *


On May 23, 10:49 am, Dmitry <dmitryp...@gmail.com> wrote:
> Hi all!
> I'm trying to edit formula in cell by following algorithm (c#):
> 1. get the r1c1 formula by  string text =
> XlCall.Excel(XlCall.xlfGetFormula, caller) as string
> 2. edit it
> 3. pate new one in the cell by XlCall.Excel(XlCall.xlcFormulaFill, new
> object[] {textNew, caller });
> Since formula separator could be either "," or ";" ... I cannot make it
> work locale independent.
>
> Before I'm starting to use excel-DNA I was using VBA and in VBA you can use
> following code
> to get cell formulaR1C1:
>  text = cell.formulaR1C1
> and it will return formula with "," separator despite user locale.
> And the same time use could insert formula "," separated and excel will
> process it right even if
> separator is ";".
> cell.formulaR1C1 = textNew
>
> Please help me figure out this situation with excel-DNA.
>
> Thx.