excel - error xlPrimary not defined in Python win32com

Question

I keep getting errors where xlCategory, xlValue and xlPrimary are not recognised in my python script.

I am trying to label the axes of my graph and was successfully doing so yesterday with this code:

chart = excel.Charts.Add()
chart.Name = "Chart Title"
chart.ChartType = -4169 #xlXYScatter
chart.SetSourceData(firstSheet.Range("$A:$B"))
series = chart.SeriesCollection(1)
series.Name = "Series Name"
chart.Axes(win32com.client.constants.xlCategory).HasTitle = True
chart.Axes(win32com.client.constants.xlCategory).AxisTitle.Caption = "x Axis"
chart.Axes(win32com.client.constants.xlValue).HasTitle = True
chart.Axes(win32com.client.constants.xlValue).AxisTitle.Caption = "y Axis"

This produced the following error:

Traceback (most recent call last):
  File "<pyshell#5>", line 1, in <module>
    startGraphBuild()
  File "C:Python33InCAS_Study_AnalysisVMDvsMODVMDG.py", line 33, in startGraphBuild
    chart.Axes(win32com.client.constants.xlCategory).HasTitle = True
  File "C:Python33libsite-packageswin32comclient\__init__.py", line 170, in 
 __getattr__ 
    raise AttributeError(a)
AttributeError: xlCategory

So I tried this from this stackoverflow question changing axis labels in excel 2007 charts using python win32com:

pAxis = chart.Axes(AxisGroup = xlPrimary)
xAxis = pAxis(1)
yAxis = pAxis(2)

xAxis.HasTitle = True
yAxis.HasTitle = True
xAxis.AxisTitle.Caption = "VMD"
yAxis.AxisTitle.Caption = "MOD VMD"

But this produced the following error:

Traceback (most recent call last):
  File "<pyshell#3>", line 1, in <module>
    startGraphBuild()
   File "C:Python33InCAS_Study_AnalysisVMDvsMODVMDG.py", line 37, in startGraphBuild
    pAxis = chart.Axes(AxisGroup = xlPrimary)
 NameError: global name 'xlPrimary' is not defined

Has anyone else experienced this? Since it was working yesterday I have tried restarting everything, uninstalling and reinstalling pyWin but these haven't worked.

I am using Python 3.3 and Excel 2010.

Answer 1

The constants are defined. However, they will only be loaded if you have created the COM Type Library for the COM objects of interest. There are several ways to do that (my self-answer to Accessing enumaration constants in Excel COM using Python and win32com has some links you'll find useful). But basically try this:

Python 2.7.5 (default, May 15 2013, 22:43:36) [MSC v.1500 32 bit (Intel)] on win
Type "help", "copyright", "credits" or "license" for more information.
Portable Python >>> import win32com
Portable Python >>> win32com.__gen_path__ # path to COM typelib generated by win32com
'C:\Users\ADMINI~1\AppData\Local\Temp\gen_py\2.7'

Now try with Dispatch:

Portable Python >>> from win32com import client
Portable Python >>> xl=client.Dispatch('Excel.Application')
Portable Python >>> client.constants.xlPrimary
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "G:Portable Python 2.7.5.1Applibsite-packageswin32comclient\__init_
__getattr__
    raise AttributeError(a)
AttributeError: xlPrimary

Now use EnsureDispatch from gencache:

Portable Python >>> xl=client.gencache.EnsureDispatch('Excel.Application')
Portable Python >>> client.constants.xlPrimary
1
Portable Python >>>

You only need to use EnsureDispatch once, since once the Type library has been created, even Dispatch will load the constants.

If you need to clear the cache for whatever reason, wasn't easy to find, but you can remove the gen_py folder, its path can be found from win32com.__gen_path__.