excel find last column

[Tim Golden]

Lance Hoffmeyer wrote:
> I ran makepy.py and loaded Microsoft Excel Object Library 11.0
> I have imported:
> 
> import win32com.client
> from win32com.client import constants
> import re
> import codecs,win32com.client
> import time
> import datetime
> import win32com.client.dynamic
> 
> 
> using this expression
> 
> lastcol = sh.UsedRange.Find("*", "A1", win32com.client.constant.SearchOrder=xlByColumns, win32com.client.constants.SearchDirection=xlPrevious).Column
> 
> I get error:
> 
> , line 245
>     lastcol = sh.UsedRange.Find("*", "A1", win32com.client.constant.SearchOrder=xlByColumns, win32com.client.constants.SearchDirection=xlPrevious).Column
> SyntaxError: keyword can't be an expression

I suspect you're getting unnecessarily (but understandably)
confused by the wrapping which the win32com does for you.

Basically, when you run the makepy stuff, a module is generated
(which you can go and look at if you feel so inclined) which
defines the operations this COM object allows. To handle the
constants, a sort of pseudo module is available to you called
win32com.client.constants which you use just like any other
Python module.

In the interpreter dump below, I import the win32com.client
stuff and force the module to be generated programatically
(essentially the same as calling makepy against the
Excel.Application COM library).

Then the constants "module" contains, among other things,
the xlByColumns constant.

<dump>
Python 2.4.4 (#71, Oct 18 2006, 08:34:43) [MSC v.1310 32 bit (Intel)] on 
win32
Type "help", "copyright", "credits" or "license" for more information.
 >>> import win32com.client
 >>> xl = win32com.client.gencache.EnsureDispatch ("Excel.Application")
 >>> win32com.client.constants.xlByColumns
2
 >>>

</dump>

If you want to you can use the usual shortcuts:

const = win32com.client.constants
print const.xlByColumns
# or even
xlByColumns = const.xlByColumns

and use that anywhere you need, so in your example:

sh.UsedRange.Find (
   "*",
   "A1",
   SearchOrder=const.xlByColumns,
   SearchDirectory=const.xlPrevious
).Column

NB I haven't bothered to see whether what your code
is doing is correct, merely illustrating the use
of constants.

HTH a bit
TJG