[Tutor] copy and paste excel worksheet using win32

[Pirritano, Matthew]

Pythonistas,

I have a nicely formatted report in excel that is designed to be filled
in by an excel macro. But first I need to get the report worksheet into
52 separate excel workbooks.

Here's what I've tried so far. I'm snipping the code a bit. I have a wx
dialog that gets a directory where the excel files live that will have
the report worksheet added to them. The variable 'infile' is really a
directory, and comes from the results of the wx dialog. Then I step
through the directory and create a list of the files. Next I start excel
and open the file with the report, then I'm trying to copy that report
into each of the files in the directory.  Below is the error I get and
the syntax.

I'm also sure each time before I run it that there is no excel process
still running.

Any help is much appreciated! Thanks,
Matt

Here's the error:

Traceback (most recent call last):
  File "C:\Projects\Copy_Worksheets_20110713.py", line 50, in <module>
    reportWs(2).Copy(None, wbWorksheets(1))
  File "C:\Python25\lib\site-packages\win32com\client\dynamic.py", line
172, in __call__
    return
self._get_good_object_(self._oleobj_.Invoke(*allArgs),self._olerepr_.def
aultDispatchName,None)
com_error: (-2147352573, 'Member not found.', None, None)


Here's the code:

fileList = ()
for infile in glob.iglob( os.path.join(infile, '*.xls') ):
    print "current file is: " + infile
    print ''
    
    fileList = fileList + (infile,)   


excel = win32com.client.Dispatch("Excel.Application")
reportWb =
excel.Workbooks.open("D:\\Data\\Excel\\BHS_Report_Format_20110713.xls")
reportWs = reportWb.Worksheets(1)

for f in fileList:
    print "processing file: " + f
    wb = excel.Workbooks.Open(f)
    wbWorksheets = wb.Worksheets(1)
    wbWorksheets.Activate()
    reportWs(2).Copy(None, wbWorksheets(1))
    wb.Close(True)

reportWb.Close(True)
exce.Quit()






Matthew Pirritano, Ph.D.
Research Analyst IV
Medical Services Initiative (MSI)
Orange County Health Care Agency
(714) 568-5648