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-11 10:48:48Z

18

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, 2018 at 10:48

answered Feb 3, 2014 at 18:35

Olav

5761 gold badge5 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Oliver Over a year ago

Excellent! This did it. Additional data doesn't fit in comment, I'll post as separate answer.

BuvinJ Over a year ago

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.

Olav Over a year ago

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

BuvinJ Over a year ago

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

WV_Mapper Over a year ago

I was able to get the constants using this approach and can access them by typing the full name in VS Code. My question: is there a way to enable auto-complete for this in VS Code?

Community · Accepted Answer · 2017-05-23 11:52:28Z

6

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

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Feb 3, 2014 at 20:08

Oliver

30k12 gold badges81 silver badges114 bronze badges

2 Comments

wlnirvana Over a year ago

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

BuvinJ Over a year ago

@Oliver: Please read the comment I placed under Olva's answer. (I thought you might know the answer to my follow up question there.)

Collectives™ on Stack Overflow

Accessing enumeration constants in Excel COM using Python and win32com

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related