How do I import a COM object namespace/enumeration

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!

Answer 1

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.

Answer 2

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.

Answer 3

All of the file format constants are documented here

Answer 4

As a general rule I find it really useful to pre-write 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.