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

[Joe Goldthwaite]

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)
   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