M>NamePtr name = spTheWorkbook->GetNames()->Add();

M>//Пробовал делать вот так...
M>//NamesPtr names = spTheWorkbook->GetNames();
M>//NamePtr name = names->Add( _variant_t("TestName") );

M>//И вот так...
M>//NamePtr name = m_spExcelApp->GetNames()->Add( _variant_t("TestName") );
M>

expression.Add(Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local)

expression Required. An expression that returns a Names object.

Name Optional Variant. Required if NameLocal isn’t specified. The text to use as the name (in the language of the macro). Names cannot include spaces and cannot look like cell references.

RefersTo Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the macro, using A1-style notation). Note Nothing is returned if the reference does not exist.
...

spTheWorkbook->GetNames()->Add(_variant_t("AAA"), _variant_t("=Лист1!$A$1:$A$5"));