I think I'm doing something fundamentally wrong here. I want to grab the values in a given range of cells without using a loop (getting/setting values for large amounts of data seems very slow using loops, so I figured I'd try to grab big blocks of data at a time).
As a small example, let's say I have an Excel spreadsheet with values in cells A1, A3, and A5. I would like to pull those values into a list. But unfortunately, when I execute the code below, I'm only getting the value of A1. Not any of the others.
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Add()
ws = wb.Worksheets("Sheet1")
val = wb.ActiveSheet.Range('A1,A3,A5').Value
print val
If I were to say, want a contiguous list of values, the following line does that:
val = wb.ActiveSheet.Range('A1:A5').Value
And it gives me a list. But since the values I want may not be contiguous, this approach won't work. The values will always be from the same column (in this example, "A"), but not necessarily contiguous rows.
Hopefully that makes sense. If not, I can clarify. Thank you for any and all help!! :)
I'm not sure if it should work...
In VBA if you try something like
values = wkstest.Range("A3,A5,A7").Value
I still only get the value of A3 returned.
Although
values = wkstest.Range("A3,A5,A7").Select
Does select the 3 cells.
I think this is a limitation in the Excel COM interface rather than the language.
Would it not be possible to extract a contiguous range and then extract the bits you want in python from the list? This is still a massive speed gain from reading each cell one at a time, even though I'm reading in 'too many' and doing some more manipulation of variables after.
I assume that in your full python code you're combining values to create the text string "A1,A3,A5" rather than having it hard coded. If you have a list of row number like rows=[1,3,5] then you could do something like:
rows=[1,3,5]
val = wb.ActiveSheet.Range('A1:A5').Value
vals = {} #create a dictionary
for i in xrange(0,len(rows)):
vals[rows[i]]=val[rows[i]-1]