Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel 'LET' function not working using openpyxl

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.

like image 666
Eb J Avatar asked Oct 16 '25 11:10

Eb J


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)"
like image 133
Eb J Avatar answered Oct 19 '25 00:10

Eb J



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!