Setting the cell name programmatically as a while loop condition when using Excel Python xl() function

Question

When I hard code this,

a = xl("A1")

Python under Excel works fine. When I code this instead,

c = "A1"

a = xl(c)

I get this error

KeyError: 'A1'

Frustrating.

Answer 1

This is by design in custom xl() function. Hope Microsoft will add more custom function for Python Excel.

PY function - Microsoft Support

Answer 2

I think this is related to the way xl() and Python integration has been implemented. It appears there is some sort of preprocessing of the xl() function, i.e. all the xl() function calls are resolved before the rest of the Python code is executed. This implies the Python code must be "read-only' and as such cannot update Excel cell contents due to the preprocessing design constraint. Two gotcha's for the price of one design snafu,

1. Cannot use variables as arguments to the xl() function, and,
2. Python running withing Excel cannot update values in Excel cells.

Answer 3

I have to say, I struggle a bit with this. Seems like a string should be a string. I have several tables and would like to load them by using variable names. I also have a bunch of assumptions and would like to load them in a loop. If a string is not a string, then these approaches, like the OP's, just won't work. It also introduces a required level of hard-coding, which is bad in Excel and worse in programming, IMHO.

Answer 4

Thank you, when will this be fixed?

Also, I couldn't find the refence documentation you've so kindly listed. Presumably it was placed somewhere obvious by the Vogon planning department of Alpha Centauri. Nonetheless a link to it would be awesome.

Answer 5

This is caused by xl() function not accept variable.