Forum Discussion
Python in Excel iteration of sheets
Hello and thank you for any assistance.
I have 1 in A1 and 2 in A2 of Sheet2
Additionally, I have the same setup in Sheet3.
for i in range(2,4):
value = f"Sheet{i}!A1:A2"
print(xl(value))
returns
KeyError: 'Sheet2!A1:A2'
Noting that it cannot find Sheet2!A1:A2 because it does not exist despite the fact it does.
for i in range(2,4):
value = f"Sheet{i}!A1:A2"
print(xl(value))
print(xl("Sheet2!A1:A2"))
However, returns
KeyError: 'Sheet3!A1:A2'
0
0 1
1 2
0
0 1
1 2
Indicating it is now able to find Sheet2!A1:A2.
And of course
for i in range(2,4):
value = f"Sheet{i}!A1:A2"
print(xl(value))
print(xl("Sheet2!A1:A2"))
print(xl("Sheet3!A1:A2"))
Prints all the correct values twice with no error.
Is this a bug? How can I iterate through all the sheets using a loop without needing to directly initialize the fact it exists?
Python in Excel doesn't work with such kind of indirect references, i.e.
a = "Sheet2!A1:A2" xl(a)returns the same error. xl() accepts direct names of the Excel objects.
8 Replies
- Sheet2!A1:A2' May be something spelling mistakes similar to below situation. https://club.excelhome.net/forum.php?mod=viewthread&tid=1682740&fromguid=hot&extra=&mobile=
Thank you for a response.
I would agree with you if it weren't for the fact that it works after another direct loading of those specific cells line pointing to the sheet.
for i in range(2,4): value = f"Sheet{i}!A1:A2" print(xl(value))Does not work for Sheet2, however,
xl("Sheet2!A1:A2") for i in range(2,4): value = f"Sheet{i}!A1:A2" print(xl(value))For Sheet2 does work
xl("Sheet2!A1:A3") for i in range(2,4): value = f"Sheet{i}!A1:A2" print(xl(value))Does not work for Sheet2
Very strange.
Yes I am having the same problem - getting a "key error" when trying to use xl() with a variable. I would love it if someone had a way to make this work. Or if it could be added in a future update. I have also noticed that it sort of works if you hardcode the cells elsewhere the load them using xl() which is very strange but not super helpful.
