[python-win32] testMSOffice.py and Excel 2003

[Tim Roberts]

On Fri, 23 Sep 2005 09:23:18 -0400, "Cavanagh, Mike" 
<mike.cavanagh at siemens.com> wrote:

>Thanks for the reply, the test case now passes.
>
>However, our internal application is still failing.
>
>code snippet:
>
>import win32com
>import win32com.client.dynamic
>import pythoncom
>from pywintypes import Unicode
>from win32com.client import gencache
>
>xl = win32com.client.dynamic.Dispatch("Excel.Application")
>xl.Visible = 1
>xlBook = xl.Workbooks.Open('C:\\test.xls')
>xlSheet = xlBook.Worksheets(1)
>print xlSheet.Name
>xlSheet.Cells(1,1).Value = "Hello World"
>print xlSheet.Cells(1,1).Value
>
>lastCol = 14
>lastRow = xlSheet.Cells.Find('Hello World',xlSheet.Cells(1, 1), 1, 1).Row
>data = xlSheet.Range(xlSheet.Cells(1,1),
>xlSheet.Cells(lastRow,lastCol)).Value
>
>xl.Workbooks(1).Close(SaveChanges = 1)
>xl.Quit()
>
>/snippet
>
>When this is run, I receive the following error:
>
>Traceback (most recent call last):
>  File "<stdin>", line 1, in ?
>  File "c:\python24\lib\site-packages\win32com\test\testExcel.py", line 16,
>in ?
>
>    lastRow = xlSheet.Cells.Find('Hello World',xlSheet.Cells(1, 1), 1,
>1).Row
>  File
>"c:\python24\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-00
>0000000046x0x1x5\Range.py", line 197, in Find
>    , MatchCase, MatchByte, SearchFormat)
>pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None,
>None,
> 0, -2147352565), None)
>
>I believe this error is coming from Excel, but I am not sure why.
>  
>

-2148352565 is 0x8002000B, which is DISP_E_BADINDEX.  The problem is the 
third parameter in your call to Find.  This is the "LookIn" parameter, 
which tells it whether to search formulas, values, or comments.  1 is 
not a legal value for this parameter.  It needs to be either xlFormulas 
(-4123) or xlValues (-4163).

You should probably use symbolic constants instead of integers.  Instead 
of using dynamic.Dispatch, if you say this:
    from win32com.client import gencache, constants
    xl = gencache.EnsureDisplatch("Excel.Application")
now you can say this:
    lastRow = xlSheet.Cells.Find('Hello World', xlSheet.Cells(1, 1), 
constants.xlFormulas, constants.xlWhole ).Row
and your code runs.

-- 
Tim Roberts, timr at probo.com
Providenza & Boekelheide, Inc.