Excel 'LET' function not working using openpyxl

user_9214004 0
Viewed 152

I am working on automating a few processes using Openpyxl as it is the most widely(maybe only) library used that has support for Excel formulas.

I have used a variety of functions in formulas in the program and they work pretty well. However, the LET function leads to 2 scenarios:

When I use it as is ie:

...... # boilerplate code
sheet.cell(row=1, column=1).value = "=LET(MYVAL,\"Boogawooga\",MYVAL)"

It updates the formula as =@LET(MYVAL,"Boogawooga",MYVAL) and I get a #NAME? error on the cell(s).

When I prefix it with _xlfn ie:

...... # boilerplate code
sheet.cell(row=1, column=1).value = "=_xlfn.LET(MYVAL,\"Boogawooga\",MYVAL)"

I get the following error upon opening the excel doc, and the cell(s) with the formula are empty: enter image description here

Using:

  • openpyxl==3.0.7
  • python 3.6
  • Excel 365(Let function works on the file directly so it's not an old version issue)

Any help will be highly appreciated.

openpyxl excel-formula excel python
1 Answers

SOLUTION:

It looks like, in addition to _xlfn, you might need the _xlpm prefix for variables!

This works:

sheet.cell(row=1, column=1).value = "=_xlfn.LET(_xlpm.MYVAL,\"Boogawooga\",_xlpm.MYVAL)"
user_92140040
Related