Excel 'LET' function not working

[Eb J]

Hi everyone, I am new to this group. I am working on automating a few processes using Openpyxl

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: 

' We found a problem with some content in 'filename.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes'

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.

[Eb J]

Sorry for the poor format of the code snippets.

These are the 2 snippets:

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

and

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

Thanks in advance

[Charlie Clark]

On 24 Jun 2021, at 21:15, Eb J wrote:

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.

Unzip the XLSX file and look at the source for the relevant worksheet. Excel uses the @ for a heap of different "special" functions.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Mobile: +49-178-782-6226

[Charlie Clark]

On 24 Jun 2021, at 21:57, Charlie Clark wrote:

Unzip the XLSX file and look at the source for the relevant worksheet. Excel uses the @ for a heap of different "special" functions.

FWIW, because I don't have Office 365 and am not currently planning to get it either. I just looked at a sample file from Goskills https://www.goskills.com/Excel/Resources/Excel-LET-function

This is in the source for F2

<f>_xlfn.LET(_xlpm.SALES,SUM(C2:D2),IF(_xlpm.SALES>5,_xlpm.SALES10,_xlpm.SALES5))</f>

It looks like, in addition to _xlfn, you might need the _xlpm prefix for references.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489

Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Eb J]

Than you so much Charlie, you are a lifesaver!

I appreciate you going out of your way to try and help me find a solution.
I have understood what the fix is. I prefixed the references/variables in the LET functions with _xlpm and it worked like a charm..

I am still unsure of how to get to see the source of a sheet, like you have mentioned. Could you please point me to any resources that show a guide on doing that so that I can independently solve a similar problem in the future? 

Cannot stress enough how grateful I am. Thank you loads

[David Hartsough]

Eb,

  I am not sure if this what you are after, but I will take a shot. If you change the file extension on your .xlsx file to .zip you can open the zip archive with a standard piece of software. An Excel workbook is simply an archive of various xml documents. Within this tree of documents are xml files defining worksheets, images, formatting,….  There are open office standards (http://officeopenxml.com/) that describe the underlying documents. These are not necessarily easy to traverse at the beginning, but everything you need to understand all the contents of the workbook is there. 

-dave 

Sent from my iPhone

On Jun 25, 2021, at 3:07 PM, Eb J <ebzj...@gmail.com> wrote:

Than you so much Charlie, you are a lifesaver!

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/f2d37ebe-faf9-48ff-9cee-a5abad4f3610n%40googlegroups.com.

[Eb J]

Hi David. That is exactly what I was looking for. Thanks a ton for your help. 

You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/O746AjGV9EY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/EC661349-D1D3-464F-A683-BB30C807FDBF%40gmail.com.

[Eb J]

UPDATE: I ran into another issue and I solved it thanks to your guys' guidance on this. 
PS getting to see the source xml for the sheets feels like using a cheat code on GTA :'D

[Charlie Clark]

On 25 Jun 2021, at 20:21, David Hartsough wrote:

> Eb,
>
> I am not sure if this what you are after, but I will take a shot. If you change the file extension on your .xlsx file to .zip you can open the zip archive with a standard piece of software. An Excel workbook is simply an archive of various xml documents. Within this tree of documents are xml files defining worksheets, images, formatting,…. There are open office standards (http://officeopenxml.com/) that describe the underlying documents. These are not necessarily easy to traverse at the beginning, but everything you need to understand all the contents of the workbook is there.

There's actually no need to rename the file, you can just open it with any kind of archival program and I'd advise this, because it will let you work with a copy, leaving the original file untouched.

On MacOS I always use unzip -d xxxx file.xlsx but I understand that this isn't directly possible in Windows, even though the system can unzip files.