Capiez Fabrice
2005-06-28 09:03:17 UTC
Hello List,
I have searched the internet and this list's archives
without success before writing this email. I apologise in
advance if I neglected something
obvious. (I googled the list's archives with "Excel" and
"chart" as keywords)
My problem is that I want to create or modify the title of
an Excel chart through win32com, as well as axes labels.
I am quite new to win32com and was initially enthousiastic
about being able to take VBA out of my project, doing
everything in python.
Before starting to rewrite all the code plus some new
features in python, I did a few tests and managed
successfuly to create some charts, add and
remove series etc. These tests showing good promised, I
entered the main phase of my project, leaving all the
cosmetics bits for the end. Now I
need to take care of the afore-said cosmetic bits and just
found out I could not access a chart's title which
jeopardises the whole thing.
The details are given underneath. My question is : "is
this a problem with win32com that cannot access all the
chart object's feantures ? or is it a
problem with my system ? or am I doing somethin altogether
wrong ?"
here is a sample of code that reproduces the error on my
computer :
# coding=utf8
import win32com.client
from win32com.client import constants
import random
import pythoncom
xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Visible = 1
wb=xlApp.Workbooks.Add()
sheet = wb.Sheets(1)
sheet.Name="toto"
for i in range (25) :
a=sheet.Cells(4+i,1)
a.Value=i
a=sheet.Cells(4+i,2)
a.Value=i**0.5*random.random()
chart = wb.Charts.Add()
chart.ChartType = constants.xlXYScatterSmoothNoMarkers
chart.Name = "Test"
series = chart.SeriesCollection().NewSeries()
Xval=sheet.Range("A4:A28")
Yval=sheet.Range("B4:B28")
series.XValues = Xval
series.Values = Yval
series.Name = "Data"
xAxis = chart.Axes()[0]
yAxis = chart.Axes()[1]
xAxis.HasMajorGridlines = True
yAxis.HasMajorGridlines = True
chart.Location (Where=constants.xlLocationAsObject,
Name=sheet.Name)
try:
chart.HasTitle = True
chart.ChartTitle.Characters.Text ="title"
except pythoncom.com_error, (hr,msg,exc,arg):
print "Excel failed with code %d: %s"
%(hr,msg)
if exc is None:
print "No extended information"
else :
wcode,source,text,helpFile,HelpId,scode=exc
print "the source of error is", source
print "message :", text
The following produces:
Excel failed with code -2146827864: OLE error 0x800a01a8
No extended information
The same error occurs if I try to set an axis title
instead of the chart's title. It also occurs whether I
first set chart.HasTitle to True or not. I
checked the chart object's model and I am using the method
names given by the analysis of a VBA macro.
The error occurs whether I use early or late binding (I
tried once to add .dynamic. before the Dispatch method)
Actually, this is not all, I discovered another side
effect :
Not being able to set the title and axis labels from
Python, I decided to create some blank charts as templates
with proper titles and labels and then
import them, copy the sheet in my active workbook and
insert my data series in them. This works as far as
inserting the series is concerned, but the
Axis labels are lost when copying the template sheet in
the current workbook (template.Copy(Before=sheet)).
Actually, the result is quite erratic
since some Axis Labels are sometimes kept intact. (I have
12 charts on my template and sometimes the 12 charts loose
their labels, sometimes 2 or
3 manage to keep them in the copy process).
So that brings me back to my initial request : could
someone try to reproduce the problem to see whether it has
something to do with win32com
that might not be able to access all legal methods or
whether it is something on my system. By the way, I am
using a Japanese system which
explains the "# coding=utf8" at the begining of the
script and might also be a source of problems.
I hope that someone is able to give me some insight on
this
Best Regards,
Fabrice Capiez
__________________________________
Save the earth
http://pr.mail.yahoo.co.jp/ondanka/
I have searched the internet and this list's archives
without success before writing this email. I apologise in
advance if I neglected something
obvious. (I googled the list's archives with "Excel" and
"chart" as keywords)
My problem is that I want to create or modify the title of
an Excel chart through win32com, as well as axes labels.
I am quite new to win32com and was initially enthousiastic
about being able to take VBA out of my project, doing
everything in python.
Before starting to rewrite all the code plus some new
features in python, I did a few tests and managed
successfuly to create some charts, add and
remove series etc. These tests showing good promised, I
entered the main phase of my project, leaving all the
cosmetics bits for the end. Now I
need to take care of the afore-said cosmetic bits and just
found out I could not access a chart's title which
jeopardises the whole thing.
The details are given underneath. My question is : "is
this a problem with win32com that cannot access all the
chart object's feantures ? or is it a
problem with my system ? or am I doing somethin altogether
wrong ?"
here is a sample of code that reproduces the error on my
computer :
# coding=utf8
import win32com.client
from win32com.client import constants
import random
import pythoncom
xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Visible = 1
wb=xlApp.Workbooks.Add()
sheet = wb.Sheets(1)
sheet.Name="toto"
for i in range (25) :
a=sheet.Cells(4+i,1)
a.Value=i
a=sheet.Cells(4+i,2)
a.Value=i**0.5*random.random()
chart = wb.Charts.Add()
chart.ChartType = constants.xlXYScatterSmoothNoMarkers
chart.Name = "Test"
series = chart.SeriesCollection().NewSeries()
Xval=sheet.Range("A4:A28")
Yval=sheet.Range("B4:B28")
series.XValues = Xval
series.Values = Yval
series.Name = "Data"
xAxis = chart.Axes()[0]
yAxis = chart.Axes()[1]
xAxis.HasMajorGridlines = True
yAxis.HasMajorGridlines = True
chart.Location (Where=constants.xlLocationAsObject,
Name=sheet.Name)
try:
chart.HasTitle = True
chart.ChartTitle.Characters.Text ="title"
except pythoncom.com_error, (hr,msg,exc,arg):
print "Excel failed with code %d: %s"
%(hr,msg)
if exc is None:
print "No extended information"
else :
wcode,source,text,helpFile,HelpId,scode=exc
print "the source of error is", source
print "message :", text
The following produces:
Excel failed with code -2146827864: OLE error 0x800a01a8
No extended information
The same error occurs if I try to set an axis title
instead of the chart's title. It also occurs whether I
first set chart.HasTitle to True or not. I
checked the chart object's model and I am using the method
names given by the analysis of a VBA macro.
The error occurs whether I use early or late binding (I
tried once to add .dynamic. before the Dispatch method)
Actually, this is not all, I discovered another side
effect :
Not being able to set the title and axis labels from
Python, I decided to create some blank charts as templates
with proper titles and labels and then
import them, copy the sheet in my active workbook and
insert my data series in them. This works as far as
inserting the series is concerned, but the
Axis labels are lost when copying the template sheet in
the current workbook (template.Copy(Before=sheet)).
Actually, the result is quite erratic
since some Axis Labels are sometimes kept intact. (I have
12 charts on my template and sometimes the 12 charts loose
their labels, sometimes 2 or
3 manage to keep them in the copy process).
So that brings me back to my initial request : could
someone try to reproduce the problem to see whether it has
something to do with win32com
that might not be able to access all legal methods or
whether it is something on my system. By the way, I am
using a Japanese system which
explains the "# coding=utf8" at the begining of the
script and might also be a source of problems.
I hope that someone is able to give me some insight on
this
Best Regards,
Fabrice Capiez
__________________________________
Save the earth
http://pr.mail.yahoo.co.jp/ondanka/