Accessing enumeration constants in Excel COM using Python and win32com

Question

I'm using python 2.7 win32com module to load an MS Excel worksheet from Python:

    book = xlApp.Workbooks.Open("myFile.xls")
    sheet = book.Sheets(1)

Many methods and properties of Range, Worksheet etc use enumerations like XlDirection, XlFillWith, and so forth. These define constants such as xlDown, xlUp, xlFillWithContents, etc. Are those constants available from win32com so that I could do, for example:

    column = outputsSheet.Range("I5:I150")
    lastRow = column.End(xlInterop.xlDown)
    print "Last row:", lastRow.Row

This doesn't work because xlInterop is not defined, is there a way to access it using win32com? Discovering the values of such constants as xlDown by trial and error is not very practical.

Olav · Accepted Answer · 2018-10-11T10:48:48.740

16

I'm new to win32com, so I don't know if this helps... try to start Excel with:

xlApp = win32com.client.gencache.EnsureDispatch("Excel.Application")

This should run makepy, and you can find some resulting Python files in ...\Lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x7 (last folder might be another name for you, I dont know). Check the init.py file, there's a bunch of constants defined there.

Edit: you can access these constants with:

from win32com.client import constants as c

Edit2: path to folder:

win32com.__gen_path__

edited Oct 11 '18 at 10:48

answered Feb 03 '14 at 18:35

Olav

547
1
5
17

Excellent! This did it. Additional data doesn't fit in comment, I'll post as separate answer. – Oliver Feb 03 '14 at 19:56
Good answer. But... I'm not finding a `gen_py` directory, or anything generated in my site packages. Yet, this did solve the problem. I'm worried about using this in standalone context after bundling with py2exe or pyinstaller. Since supposedly this generates .py files (and then loads them)? Has anyone done that before? Or least know of any simple way to point the generation / utilization of these .py files to a temp directory perhaps since I can't expect a python installation on the client machine? I could dig into the library and customize it, but a ready solution would be better. – BuvinJ Oct 09 '18 at 14:54
@BuvinJ: It seems they have changed the outputfolder for makepy. If I run this I get the folder: `win32com.__gen_path__` Here the folder is 'C:\\Users\\Olav\\AppData\\Local\\Temp\\gen_py\\3.6' I can't help you with the other questions. – Olav Oct 11 '18 at 10:44
Thanks! That's very useful. So, they are at least writing to a temp folder now. Excellent. I'll check my `win32com.__gen_path__` value. I've never encountered a scenario quite like this in regards to running within a compiled context where a script was generated and imported on the fly. But, I know that py2exe and pyinstaller embed an interpreter and are capable of executing `eval` statements. So, there must be way to import this post generation. At the worse, I'll tweak the library so it skips the file writing entirely I guess... – BuvinJ Oct 11 '18 at 12:27

score 6 · Answer 2 · edited May 23 '17 at 11:52

Olav's answer enabled me to do a search on keywords "win32com gencache EnsureDispatch constants" and I found a few useful links. Like How do I import a COM object namespace/enumeration in Python?, which my question duplicates. This points to an ActiveState page Quick Start to Client side COM and Python with intro to this stuff. Oddly, that page states that client.Dispatch should be sufficient to get constnats, but I found it wasn't, I had to run gencache.EnsureDispatch, only then did the constants appear:

Portable Python >>> from win32com import client as com
Portable Python >>> from win32com.client import constants as c
Portable Python >>> word = com.Dispatch('Word.Application')
Portable Python >>> c.wdWindowStateMinimize
  Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
    File "F:\PORTAB~1.1\App\lib\site-packages\win32com\client\__init__.py", line 170, in __getattr__
      raise AttributeError(a)
  AttributeError: wdWindowStateMinimize
Portable Python >>> word = com.gencache.EnsureDispatch("Word.Application")
Portable Python >>> c.wdWindowStateMinimize
2

The ActiveState page you mentioned states that you have to generated those files first. After that you can access the constants with only `Dispatch` and `constants` of `win32com.client` — wlnirvana, Feb 17 '15 at 07:33
@Oliver: Please read the comment I placed under Olva's answer. (I thought you might know the answer to my follow up question there.) — BuvinJ, Oct 09 '18 at 14:58

Accessing enumeration constants in Excel COM using Python and win32com

2 Answers2

Linked