0

I have a complicated workbook that I am generating with Openpyxl. I've decided to revise the formulas to use Excel's so-called future functions and array functions. Some of the formulas are getting pretty long and I was able to use the new LAMBDA function attached to a name (set in Name Manager). I was able to read the defined name definition from the saved workbook, but attempting to create a new one seems to prevent Excel from opening the file (with a generic message).

The Openpyxl documentation on defined names only talks about sets of ranges. No reference to lambda functions.

In test_wb.xlsm in Excel's Name Manager I created a name, DOB, and set the Refers to field as =LAMBDA(a,XLOOKUP(a,tbl_people[Initials],tbl_people[DOB]))

In Excel the following formula now produces a date of birth: DOB("VEC"). Tidy.

The following creates a broken workbook

'''experiment with defined names'''
from openpyxl import load_workbook
from openpyxl.workbook.defined_name import DefinedName
wb=load_workbook('data/test_wb.xlsm')
dns=wb.defined_names
dob=dns['DOB']
d=DefinedName("XYZ",comment='test lambda',attr_text=dob.attr_text)
wb.defined_names.add(d)
wb.save("data/dn_book.xlsm")

I copied the existing defined name attribute text under XYZ just to make sure I hadn't mangled the formula.

I suspect that it is not possible to create a named lamda function with the current release of Openpyxl. Can this be comfirmed?

Cronical
  • 1
  • 2

1 Answers1

-1

I realized that I had not opened the source file with keep_vba=True and named the output as xlsm. This is what caused the problem, not any problem with openpyxl.

In fact, I'm pleased to say that putting a lambda expression as the reference in a defined name appears to works fine as long as you get the _xlfn and _xlpm prefixes in place like this:

_xlfn.LAMBDA(_xlpm.a,_xlfn.XLOOKUP(_xlpm.a,tbl_people[Initials],tbl_people[DOB]))
Cronical
  • 1
  • 2