{Spam?} RE: [python-win32] Can't figure out how to get the Excel copy method to work correctly.

[Bob Gailer]

At 12:14 PM 8/23/2004, Joe Goldthwaite wrote:
>It is strange that you got the error message on the count method.

Case sensitivity gets "turned on" as a result of makepy. If you did not run 
makepy for Excel then it won't matter.

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

That should guarantee that XLWorksheets(cnt) is a worksheet.

>The worksheet is still getting copied to a new workbook instead of the end 
>of the current workbook.

Consider Excel Help:
expression.Copy(Before, After)
[snip]
Before   Optional Variant. The sheet before which the copied sheet will be 
placed. You cannot specify Before if you specify After.
After   Optional Variant. The sheet after which the copied sheet will be 
placed. You cannot specify After if you specify Before.
Remarks
If you don't specify either Before or After, Microsoft Excel creates a new 
workbook that contains the copied sheet.

Based on this it sounds like Excel is not getting the Before or after. Try:

XLWorksheets(1).Copy(None, XLWorksheets(cnt)) # treat before,after as 
postional parameters.

>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
>
>_______________________________________________
>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 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/python-win32/attachments/20040823/e845a2bb/attachment-0001.htm