Newbie: returning dynamicly built lists (using win32com)
Very newb here, but my question will hopefully be obvious to someone.
Code:
import string
from win32com.client import Dispatch
docdir = 'E:\\scripts\\Python\\RSAutomation\\'
def getOldData(testcases):
excel = Dispatch("Excel.Application")
excel.Workbooks.Open(docdir + 'FILE.xls')
# load and create list from file (testcases.csv)
for rsinput in testcases.xreadlines():
inputlist = string.split(rsinput, ',')
# iterate through and update spreadsheet input
cellx = range(3,51)
values = range(0,48)
for i,r in zip(cellx, values):
excel.ActiveSheet.Cells(i,2).Value = inputlist[r]
# TODO: read output from cell 32,6 into a tuple or list and
then return list to __main__
[THIS IS WHERE I AM HAVING A PROBLEM]
print excel.ActiveSheet.Cells(32,6) <--This prints properly
as loop executes
excel.ActiveWorkbook.Close(SaveChanges=0)
excel.Quit()
if __name__ == "__main__":
csv_testcases = open('arse_testcases.csv','r')
getOldData(csv_testcases)
OK, so what is happening is that I am sending a list of data to an
overly complicated spreadsheet that produces it's own output (in cell
32,6). As I loop through multiple test cases, the print statement
calling into COM for the cell data seems to be printing out results
just fine. But when I try and put the output from the spreadsheet into
a dynamic list after the TODO section thusly:
outputlist = []
outputlist.extend(excel.ActiveSheet.Cells(32,6)
return outputlist
I get an error like:
[]
I need to be able to return the dynamically generated built up by the
responses from the spreadsheet lookup call (the exce.Activesheet
thingy). Is there a better way to get this dynamically built list out
of the funtion?
Thanks!!!
--
http://mail.python.org/mailman/listinfo/python-list
Re: Newbie: returning dynamicly built lists (using win32com) (SOLVED)
Thanks folks! I had just gotten myself into a blind rut, apparently.
Adding the .Value attribute to the com object does strip all the other
messaging returning from Excel so I could then populate my list and
return out of the function normally.
I had tried that earlier, but had used the .Value attribute incorrectly
( Cells.Value(x,y) instead of Cells(x.y).Value.
Cheers,
G
Ransom wrote:
> Very newb here, but my question will hopefully be obvious to someone.
>
> Code:
>
> import string
> from win32com.client import Dispatch
> docdir = 'E:\\scripts\\Python\\RSAutomation\\'
>
> def getOldData(testcases):
>
> excel = Dispatch("Excel.Application")
> excel.Workbooks.Open(docdir + 'FILE.xls')
>
> # load and create list from file (testcases.csv)
> for rsinput in testcases.xreadlines():
>
> inputlist = string.split(rsinput, ',')
>
>
> # iterate through and update spreadsheet input
> cellx = range(3,51)
> values = range(0,48)
> for i,r in zip(cellx, values):
>
> excel.ActiveSheet.Cells(i,2).Value = inputlist[r]
>
> # TODO: read output from cell 32,6 into a tuple or list and
> then return list to __main__
>
> [THIS IS WHERE I AM HAVING A PROBLEM]
> print excel.ActiveSheet.Cells(32,6) <--This prints properly
> as loop executes
>
> excel.ActiveWorkbook.Close(SaveChanges=0)
> excel.Quit()
>
> if __name__ == "__main__":
> csv_testcases = open('arse_testcases.csv','r')
> getOldData(csv_testcases)
>
> OK, so what is happening is that I am sending a list of data to an
> overly complicated spreadsheet that produces it's own output (in cell
> 32,6). As I loop through multiple test cases, the print statement
> calling into COM for the cell data seems to be printing out results
> just fine. But when I try and put the output from the spreadsheet into
> a dynamic list after the TODO section thusly:
>
> outputlist = []
> outputlist.extend(excel.ActiveSheet.Cells(32,6)
> return outputlist
>
> I get an error like:
> [ 0x15450880>]
>
> I need to be able to return the dynamically generated built up by the
> responses from the spreadsheet lookup call (the exce.Activesheet
> thingy). Is there a better way to get this dynamically built list out
> of the funtion?
>
> Thanks!!!
--
http://mail.python.org/mailman/listinfo/python-list
Re: Newbie: returning dynamicly built lists (using win32com)
> 1. First of all, this is not the code you are running. I know this because > the unbalanced parens wont even compile. It really doesn't help when you > ask for help, but post the wrong code. "Ok! Ok! I must have, I must have put a decimal point in the wrong place or something. Shit. I always do that. I always mess up some mundane detail." -Michael Bolton Actually, this was a typo in my e-mail. And yes, I realize how annoying that can be when someone is seeking help. Thanks for your otherwise excellent response, though! G Paul McGuire wrote: > "Ransom" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > Very newb here, but my question will hopefully be obvious to someone. > > > But when I try and put the output from the spreadsheet into > > a dynamic list after the TODO section thusly: > > > > outputlist = [] > > outputlist.extend(excel.ActiveSheet.Cells(32,6) > > return outputlist > > > > I get an error like: > > [ > 0x15450880>] > > > > 1. First of all, this is not the code you are running. I know this because > the unbalanced parens wont even compile. It really doesn't help when you > ask for help, but post the wrong code. > > 2. What you are getting is NOT an error. Read it very carefully. What you > have added to outputlist is a Range object. Look into the Excel COM > documentation (you can open up VB from Excel by pressing Alt-F11, then open > the Object Browser to see the object API) for how to access the methods and > properties of an Excel Range. I'm guessing one of the properties Value, > Value2, Text, or Formula will give you what you want. > > 3. The reason your print statement appears to work is because print > implicitly applies the str method to objects, while applying the repr method > to contents of a list. So "print excel.ActiveSheet.Cells(32,6)" will output > "42" or whatever - for grins, try "print > type(excel.ActiveSheet.Cells(32,6))" (taking care to insert enough parens > :) ) > > All that is gold does not glitter, not all who wander are lost, don't judge > a book by its cover, or a variable by its output, etc... > > -- Paul -- http://mail.python.org/mailman/listinfo/python-list
occasional win32com error
Hey folks...
Newbie here. I'm working with win32com launching, closing and
re-launching Excel grabbing output and doing stuff.
Well, on some occasions, I get the following error:
Traceback (most recent call last):
File "checkrates.py", line 95, in ?
newdata = getNewData(testcases1)
File "checkrates.py", line 62, in getNewData
excel.Workbooks.Open(docdir + 'TOSrat2006_09.xls')
File "C:\Python24\lib\site-packages\win32com\client\dynamic.py", line
496, in __getattr__
raise AttributeError, "%s.%s" % (self._username_, attr)
AttributeError: Excel.Application.Workbooks
Sometimes the code runs fine. Sometimes I get this error. The code in
question is:
import string
from win32com.client import Dispatch
docdir = 'E:\\scripts\\Python\\RSAutomation\\'
def getOldData(testcases):
#open excel
excel = Dispatch("Excel.Application")
excel.Workbooks.Open(docdir + 'TOSrat2006_07.xls')
oldoutputlist = []
for rsinput in testcases.xreadlines():
inputlist = string.split(rsinput, ',')
# iterate through and update spreadheet input
cellx = range(3,51)
values = range(0,48)
for i,r in zip(cellx, values):
excel.ActiveSheet.Cells(i,2).Value = inputlist[r]
#read spreadsheet output and cat to outputlist
premium = excel.ActiveSheet.Cells(32,6).Value
oldoutputlist.append(premium)
# close up excel
excel.ActiveWorkbook.Close(SaveChanges=0)
excel.Quit()
del excel
return oldoutputlist
def getNewData(testcases):
# open excel
excel = Dispatch("Excel.Application")
excel.Workbooks.Open(docdir + 'TOSrat2006_09.xls')
newoutputlist = []
for rsinput in testcases.xreadlines():
inputlist = string.split(rsinput, ',')
# iterate through and update spreadsheet input
cellx = range(3,51)
values = range(0,48)
for i,r in zip(cellx, values):
excel.ActiveSheet.Cells(i,2).Value = inputlist[r]
# read ratesheet output and cat to outputlist
premium = excel.ActiveSheet.Cells(32,6).Value
newoutputlist.append(premium)
excel.ActiveWorkbook.Close(SaveChanges=0)
excel.Quit()
del excel
return newoutputlist
if __name__ == "__main__":
testcases = open('arse_testcases.csv','r')
testcases1 = open('arse_testcases.csv','r')
olddata = getOldData(testcases)
newdata = getNewData(testcases1)
print olddata
print newdata
It seems like Python or COM is having a hard time freeing up (or
closing down) excel prior to the "getNewData" function running and it
is stepping on itself. I thought the stuff I'm doing at the end of
getOldData should successfully shut down excel.
Any advice, criticism, flames are appreciated.
Cheers!
--
http://mail.python.org/mailman/listinfo/python-list
