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:
Using:
Let
function works on the file directly so it's not an old version issue)Any help will be highly appreciated.
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)"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With