[python-win32] Can't figure out how to get the Excel copy method to work correctly.

[Joe Goldthwaite]

It is strange that you got the error message on the count method.  I wasn't
getting any error but it looks like it's not returning a valid worksheet
object.  Here's a second attempt;

XLApp = Dispatch("Excel.Application")
XLApp.Visible = -1
XLWorkbook =
XLApp.Workbooks.Open("c:\EFS01.0\Extensions\ISRecapTemplate.xls")
XLWorksheets = XLWorkbook.Worksheets
cnt = XLWorksheets.Count
print XLWorksheets(cnt).Name
XLWorksheets(1).Copy(After=XLWorksheets(cnt))

I get the count of worksheets in the cnt variable (q) and when I print the
name of XLWorksheets(cnt), I get the correct name.  The worksheet is still
getting copied to a new workbook instead of the end of the current workbook.
It sounds like this is what you got also.  Excel is not recognizing
XLWorksheets(cnt) as a worksheet object.


-----Original Message-----
From: Bob Gailer [mailto:bgailer at alum.rpi.edu]
Sent: Monday, August 23, 2004 10:53 AM
To: joe at goldthwaites.com; Python-win32 at python.org
Subject: Re: [python-win32] Can't figure out how to get the Excel copy
method to work correctly.


At 10:44 AM 8/23/2004, Joe Goldthwaite wrote:
>Hello Everyone,
>
>This is my first post to this list.  I ran into a problem while converting
>some old VB applications to Python and I don't have a clue on how to get
>around it.  I have this sample VB subroutine;
>
>    Sub Test()
>        Set XLApp = CreateObject("Excel.Application")
>        XLApp.Visible = -1
>        Set XLWorkbook = XLApp.Workbooks.Open("c:\Someworkbook.xls")
>        Set XLWorksheets = XLWorkbook.Worksheets
>        Set LastWorksheet = XLWorksheets(XLWorksheets.Count)
>        XLWorksheets(1).Copy After:=LastWorksheet
>    End Sub
>
>
>What is does is made a copy of worksheet 1 and puts it as the last tab of
>the workbook.  The worksheet copy method says that if you specify the
>"Before" or "After" parameters, it inserts the copy before or after the
>worksheet passed.  is means that you have to pass a worksheet object to the
>copy method. If you leave the before or after parameters off, the new
>worksheet is copied to a new workbook. The above code works fine.  Here's
my
>Python equivalent;
>
>    from win32com.client import Dispatch
>
>    XLApp = Dispatch("Excel.Application")
>    XLApp.Visible = -1
>    XLWorkbook =
>XLApp.Workbooks.Open("c:\EFS01.0\Extensions\ISRecapTemplate.xls")
>    XLWorksheets = XLWorkbook.Worksheets
>    LastWorksheet = XLWorksheets(XLWorksheets.count)

At which pont I get:
AttributeError: '<win32com.gen_py.Microsoft Excel 9.0 Object Library.Sheets
instance at 0x34498360>' object has no attribute 'count'

After capitalizing Count and rerunning I get a 4th worksheet in one book.
Don't know why your mileage varies.

>    XLWorksheets(1).Copy(After=LastWorksheet)
>
>When this is run, the worksheet get's copied to a new workbook instead of
>getting appended to the existing workbook.  I know it's passing something
to
>the "After" parameter because if I spell "After" with a lower case "A", I
>get an error that it's an unknown method.  I also know that LastWorksheet
>has a reference to the last worksheet in the workbook because I can see all
>the properties.  For some reason, Excel doesnt' recognize LastWorksheet as
a
>valid worksheet reference.
>
>I suspect that the Win32 routines are doing some translating of the COM
>objects so they can work from python but doesn't do it if you pass a COM
>object as a parameter.  Does anyone have any ideas on how I can get this
>functionality working?
>
>Thanks.
>
>Joe Goldthwaite
>
>_______________________________________________
>Python-win32 mailing list
>Python-win32 at python.org
>http://mail.python.org/mailman/listinfo/python-win32

Bob Gailer
bgailer at alum.rpi.edu
303 442 2625 home
720 938 2625 cell