KeyError with Python in excel

[AhmedVermeulen]

Hello everyone,

This is my first post on the forum, so please excuse my mistakes if it's been mislabeled.

I face this KeyError in python in excel when i attpempt to obtain the value of cell AF12 (see the inserted image):
Portlantis.png
In this situation I use a variable that contains the string AF12 as input for my xl() function.

When i try to directly call the value of the same cell using the following code: target = xl('AF12'), I don't face any errors.

Can anyone tell me what's wrong with it?

All help is appreciated!

[JEC.]

How about something like

import pandas as pd
data = xl("A1:R15")
col = 5
row = 12
data = data.iloc[row-1,col-1] 

[AhmedVermeulen]

Thanks for the reply!

I think what you're doing is trying to obtain the data in the cell using pandas. This doesn't work for me, because what I eventually want is to be able to live edit the cells. That's why I am going for python in excel instead of openpyxl or pandas. I actually already made a prototype that does exactly what I want using openpyxl, but it gives me a corrupt file or one that has no links and macros. So my goal atm is to live edit instead using python in excel, but then i got this weird error.

[JEC.]

I actually don't understand what you are trying to do exactly. Maybe with an example file it will be clearer.
For example, do you really need Python here?

[AhmedVermeulen]

I can share an example file when i am at home, but i can't share the file itself as it's confidential. The idea is simple however, I have a large project (one big contract) that's made up out of smaller projects (small contracts). The small contracts aren't decided at the start of the timeline, so their number vary heavily. The goal is that excel will read the delivery dates from a column, say C, and calculate the number of cells to the right (in the planning) it has to go to reach the cell the corresponding date. Then it should color it red. I am not sure if excel can do this. I already did it with openpyxl, but it gives a corrupted file back due to how many things are going on at the same time in the document. Not to mention it's a coauthoring document which already makes openpyxl unwanted since it can't handle that.

[PrizeGotti]

You can use Pandas inside Excel. Python for Excel supports 1000s of packages.

[AhmedVermeulen]

I will try and see if it can live edit the cell and let you know, thanks!

[skywriter]

I would recommend checking out xlwings to anyone familiar with VBA who wants to use Python to interface with Excel.
xlwings.org

[AhmedVermeulen]

I actually followed through with this. I made my code in xlwings and it runs as I want it to, thanks a lot! Any ideas on how I should approach turning it into something similar to an excel function? The goal is to have it run on it's own when a new entry is made in excel. I am not familiar with VBA btw,i am just starting to code x), but I would be happy to look into it if that's what gets the job done. Thanks for the suggestion!

[AhmedVermeulen]

I tried doing that but it still saves to a new file, but I managed to get it done with xlwings. Thanks for the suggestion though!

[AhmedVermeulen]

I tried importing xlwings instead, but I don't think it's supported

[JEC.]

It looks like you need a change event in VBA...