excel find last column

[Lance Hoffmeyer]

Hi all,

I am trying to find the last used column in an excel sheet using win32com:

lastcol = sh.UsedRange.Columns.Count

works, but only if there is no formatting. I want to allow formatting in the columns.

I would rather use the code below because formatted cells are irrelevant.
using:

lastcol = sh.UsedRange.Find("*",xlPrevious,xlByColumns).Column

I get the error that xlPrevious is not defined.



I am using Activestate PythonWin 2.3.5. makepy.py cannot be located.
Is makepy.py needed? If so, where do I find it and how do I install
it? Seems like I ran into this problem before but I cannot remember
how I solved it?


Lance

 

[Luap777]

I get the error that xlPrevious is not defined.

If you are using pythonwin, run the COM Makepy utility on Microsoft
Excel Object Library. Then you have access to the defined constants as
follows.
2

Hope this helps.

Paul

 

[Lance Hoffmeyer]

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

Tool completed with exit code 1


Guess I am getting closer to the solution? LOL
Sorry for being ignorant but I am a newbie and I don't understand all this python/excel stuff.

Lance

 

[Tim Golden]

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