How do I import a COM object namespace/enumeration in Python?

Question

I'm relatively new to programming/python, so I'd appreciate any help I can get. I want to save an excel file as a specific format using Excel through COM. Here is the code:

import win32com.client as win32 

def excel():
    app = 'Excel'
    x1 = win32.gencache.EnsureDispatch('%s.Application' % app)
    ss = x1.Workbooks.Add()
    sh = ss.ActiveSheet
    x1.Visible = True
    sh.Cells(1,1).Value = 'test write'
    ss.SaveAs(Filename="temp.xls", FileFormat=56)
    x1.Application.Quit()

if __name__=='__main__':
    excel()

My question is how do I specify the FileFormat if I don't explicitly know the code for it? Browsing through the documentation I find the reference at about a FileFormat object. I'm clueless on how to access the XlFileFormat object and import it in a way that I can find the enumeration value for it.

Thanks!

Community · Accepted Answer · 2017-05-23 12:16:55Z

10

This question is a bit stale, but for those reaching this page from Google (as I did) my solution was accessing the constants via the win32com.client.constants object instead of on the application object itself as suggested by Eric. This lets you use enum constants just like in the VBE:

>>> import win32com.client
>>> xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
>>> C = win32com.client.constants
>>> C.xlWorkbookNormal
-4143
>>> C.xlCSV
6
>>> C.xlErrValue
2015
>>> C.xlThemeColorAccent1
5

Also, unless you've manually run the makepy utility, the constants may not be available if initializing the application with the regular win32com.client.Dispatch(..) method, which was another issue I was having. Using win32com.client.gencache.EnsureDispatch(..) (as the questioner does) checks for and generates the Python bindings at runtime if required.

I found this ActiveState page to be helpful.

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Mar 24, 2011 at 1:10

Greg Haskins

6,8442 gold badges30 silver badges22 bronze badges

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

2 Comments

Li-aung Yip Over a year ago

This helped me while I was driving AutoCAD from Python. Thanks.

starwarswii Over a year ago

You can also import it directly using from win32com.client import constants as C

Eric Palakovich Carr · Accepted Answer · 2009-10-29 23:38:05Z

2

When I used COM to access quickbooks, I could reach the constants defined under a constants member of the object. The code looked something like this (you'll be intersted in the third line):

self._session_manager.OpenConnection2("",
                                      application_name,
                                      QBFC8Lib.constants.ctLocalQBD)

I'm not sure if this will work, but try this:

import win32com.client as win32 

def excel():
    app = 'Excel'
    x1 = win32.gencache.EnsureDispatch('%s.Application' % app)
    ss = x1.Workbooks.Add()
    sh = ss.ActiveSheet
    x1.Visible = True
    sh.Cells(1,1).Value = 'test write'
    ss.SaveAs(Filename="temp.xls", FileFormat=x1.constants.xlWorkbookNormal)
    x1.Application.Quit()

if __name__=='__main__':
    excel()

Replace xlWorkbookNormal with whatever format your trying to choose in the X1FileFormat web page you posted in your question.

edited Oct 29, 2009 at 23:38

answered Oct 29, 2009 at 23:21

Eric Palakovich Carr

23.4k8 gold badges51 silver badges55 bronze badges

1 Comment

behindalens Over a year ago

Unfortunately, there is no constants object for Excel.

SqlACID · Accepted Answer · 2009-10-29 23:49:30Z

1

All of the file format constants are documented here

answered Oct 29, 2009 at 23:49

SqlACID

4,02423 silver badges29 bronze badges

1 Comment

behindalens Over a year ago

That's the solution I'm using now. I've seen this problem before in other COM object interaces where there isn't proper documentation. I know the name of the constant, but not the value. I'm curious if there is a way to sidestep this.

Toby Allen · Accepted Answer · 2020-08-31 06:47:39Z

1

As a general rule I find it really useful to pre-record any code in the VBA IDE in Excel. This way you can find out all the values of constants etc that you need to use within your python code. You can also make sure stuff will work from within a more controlled environment.

edited Aug 31, 2020 at 6:47

answered Nov 7, 2009 at 8:21

Toby Allen

11.3k12 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

How do I import a COM object namespace/enumeration in Python?

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related