Error when exporting budget to Excel – The call to member Add failed. Microsoft Office Excel returned the following message: That name is not valid.

Sometimes you get a question, which seems so easy, that you know the answer instantly. You need to follow simple steps and the issue will be resolved. However, just to be sure (before giving the answer) you try the solution yourself, and you bang your head into another, totally different issue.

Today, I was trying to answer how to export budgets that have been filtered by dimension to excel (with a possibility to modify the budget in excel and import it back to Dynamics NAV). Hint: it is not enough to apply a filter on Dimension Filter field, in Filters tab, in the Budget form. You need to select “Column Dimensions” on Options tab, in “Functions > Export to Excel” report’s request form. But, I will write about this some other time.

Today, when explaining, how to export the budget with “Column Dimensions” defined, I have encountered the error message: The call to member Add failed. Microsoft Office Excel returned the following message: That name is not valid.
Error when exporting Budget to Excel - The call to member Add failed

Firstly, I have checked, where the error message originates:
Error when exporting Budget to Excel - Function CreateRange

Secondly, I had a look if this code has been changed in later versions (I was getting error message in Dynamics NAV 5 SP1). Strangely, the function CreateRange in Dynmics NAV 2009 was exactly the same, but I wasn’t getting the error message in newer version.

After further analysis and research, I’ve worked out that this seems to be a known-issue for Dynamics NAV 5 SP1 (lately, I haven encountered quite a few “known-issues”) – the problem occurs when exporting a dimension code that contains blank space (or special characters) to Excel 2003/2007. This, however, works fine when exporting from Dynamics NAV 2009.

So, a workaround (until you will get upgraded to a newer version) would be to follow these steps:

1. Click Financial Management.
2. Expand General Ledger, and then click Budgets.
3. In the Budgets dialog box, click Functions, and then click Export to Excel.
4. In the Export Budget to Excel dialog box, click the Options tab.
5. In the Column Dimensions field, click the ellipsis button (…).
6. In the Dimension Selection dialog box, make sure that the dimension codes in the Code column do not contain a blank space or special characters.
7. If the dimension codes contain a blank space or special characters, rename the dimension codes. For example, if a dimension code is named “My Name,” you can rename the dimension code to “MyName” or “My_Name.”

Technorati Tags: , , , , , ,

This entry was posted in Dynamics NAV / Navision, errors and tagged , , , , , , . Bookmark the permalink.

One Response to Error when exporting budget to Excel – The call to member Add failed. Microsoft Office Excel returned the following message: That name is not valid.

  1. Akhilesh says:

    Hi Thomas ,

    Error hitting on report 82 following code
    ExcelBuf.CreateRange(ColumnDimCode[i]);

    compare the 5 version object to 2009 version Microsoft has already fixed this issue, and apply the fix you have to update 2 lines code , please check the following code , thanks

    ///AK1.0 START
    //ExcelBuf.CreateRange(ColumnDimCode[i]);
    ExcelBuf.CreateRange(‘NAV_DIM’ + FORMAT(i));
    ///AK1.0 END

    ExcelBuf.SetCurrent(DimensionRange[1,i],1);
    ExcelBuf.StartRange;
    ExcelBuf.SetCurrent(DimensionRange[2,i],1);
    ExcelBuf.EndRange;

    ///AK1.0 START
    //ExcelBuf.CreateValidationRule(ColumnDimCode[i]);
    ExcelBuf.CreateValidationRule(‘NAV_DIM’ + FORMAT(i));
    ///AK1.0 END

Leave a Reply

Your email address will not be published. Required fields are marked *