error xlPrimary not defined in Python win32com

问题:

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:\Python33\InCAS_Study_Analysis\VMDvsMODVMDG.py", line 33, in startGraphBuild
    chart.Axes(win32com.client.constants.xlCategory).HasTitle = True
  File "C:\Python33\lib\site-packages\win32com\client\__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:\Python33\InCAS_Study_Analysis\VMDvsMODVMDG.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.

回答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.1\App\lib\site-packages\win32com\client\__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__.

回答2:

The main reason for this attribute error is because your COM-server has shifted from late-binding (dynamic) to early binding (static).

• In Late Binding, whenever a method is called, the object is queried for the method and if it succeeds, then the call can be made.
• In Early Binding, the information of the object model is determined in advance from type information supplied by the object call. Early binding makes use of MakePy. Also, early binding is case sensitive.

There are two ways to fix this issue:

1. Use the dynamic module to force your code to work in a late-bound oriented way. Example use:

   "win32com.client.dynamic.Dispatch()" instead of "win32com.client.Dispatch()" 
   

2. Use camelcase sensitive keywords for the early bound oriented way. Example use:

   "excel.Visible()" instead of "excel.VISIBLE()" or "excel.visible()"
   

If you want to use variables without case sensitive issues, you should delete the gen_py folder and use win32com.client.Dispatch()