Create Defined Names from one workbook into another

59 views
Skip to first unread message

Ólavur Nón

unread,
May 28, 2024, 5:04:27 AMMay 28
to Excel-DNA
Hello Govert,

I am trying to copy defined names from one workbook to another, but i am running into problems with formula and strings. This is my code (a bit simplified):
```
public void onButtonPressed(IRibbonControl control)
{
    // Setup application.
    Excel.Application excelApp = (Excel.Application)ExcelDnaUtil.Application;
    Excel.Workbook thisWorkbook = excelApp.ActiveWorkbook;
    Excel.Workbook fromWorkbook = ... i get this workbook by using some forms and more...
    foreach (Excel.Name definedName in fromWorkbook.Names)
    {
        if (definedName.Visible == false)
        {
            continue;
        }
        var newDefinedName = thisWorkbook.Names.Add(
            Name: definedName.Name,
            RefersTo: definedName.RefersTo
        );
        newDefinedName.Comment = definedName.Comment;
    }
}
```
I get the following error when trying to add a defined name to "thisWorkbook":
```
System.Runtime.InteropServices.COMException: 'There's a problem with this formula.

Not trying to type a formula?
When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula:
- you type =1+1, cell shows: 2,
To get around this, type an apostrophe ( ' ) first:
- you type: '=1+1, cell shows =1+1
```

I have both LAMBDA functions and simple constants with decimals that i want to copy over. When i try to insert the "RefersTo" of the defined name into a cell, it is formatted correctly, and i can copy the text and create a defined name with the Excel UI.

I tried this with VBA in the Excel Developer, and there it worked! But it does not work when working with C#. I want this function to be within the add-in i am creating.

Can you help me? :) Thank you in advance!

Govert van Drimmelen

unread,
May 28, 2024, 8:06:17 PMMay 28
to Excel-DNA
Are you perhaps in an interesting language setting, and need the localized version of a function name, or not?

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/1e9811a8-4f07-4959-90dd-c91da9f4ba8en%40googlegroups.com.

Ólavur Nón

unread,
May 29, 2024, 3:30:37 AMMay 29
to Excel-DNA
Yes, many of us use Danish as language, and this is actually exactly what i need. After further investigation, i found out that the formula recieved from "definedName.RefersTo" is in a general english "programming" language, while the method "thisWorkbook.Names.Add(...) is language specific. Is there any library that can translate the formula to my localized language?

Ólavur Nón

unread,
May 29, 2024, 4:16:36 AMMay 29
to Excel-DNA
I can let you know, that currently, i have created a formatting function that formats the formula before using it in thisWorkbook.Names.Add():
```
private static string FormatFormula(string formula)
{
    CultureInfo applicationCulture = CultureInfo.CurrentCulture;
    string applicationDecimal = applicationCulture.NumberFormat.NumberDecimalSeparator;
    string applicationArgument = applicationCulture.TextInfo.ListSeparator;

    CultureInfo systemCulture = new CultureInfo("en-US");
    string systemDecimal = systemCulture.NumberFormat.NumberDecimalSeparator;
    string systemArgument = systemCulture.TextInfo.ListSeparator;

    string formattedFormula = formula
        .Replace(systemArgument, applicationArgument)
        .Replace(systemDecimal, applicationDecimal);

    return formattedFormula;

}
```
In my local application, i use English with Danish numbering notation, therefore the formulas use "," as decimal separator, and ";" as argument separator with English-named functions. When the formulas are accessed by C#, the decimal separator becomes "." and argument separator becomes ",", and function names are the same. The above "FormatFormula" method solves this exact case of mine, but it does ofcourse not work if the function names have to be renamed from English to Danish, and also the ".Replace" methods might in some cases replace ";" to "," and then "," to ".".

I am hoping there is some general library that can do all this work for me, and i only have to specify my localized application language. Does Excel-DNA have any methods?

Ólavur Nón

unread,
May 30, 2024, 3:17:01 AMMay 30
to Excel-DNA
Oh my, i though i had tried everything, but apparently, i had not used the ".RefersToLocal" property to create the Defined Name. This works!:
```
foreach (Excel.Name definedName in fromWorkbook.Names)
{

    if (definedName.Visible == false)
    {
        continue;
    }

    var newDefinedName = thisWorkbook.Names.Add(
        Name: definedName.Name,
        RefersTo: definedName.RefersToLocal
    );

    newDefinedName.Comment = definedName.Comment;

}
```

Reply all
Reply to author
Forward
0 new messages