1

I need to change the formula of a chart in excel using python, to find out how to do it I recorded a macro and this is what I got :

ActiveSheet.ChartObjects("Graphique 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Graphique 1").Activate
ActiveChart.SeriesCollection(1).Values = "='Données'!$ET$68:$IJ$68"
ActiveChart.SeriesCollection(1).XValues = "='Données'!$ET$1:$IJ$1"

So my chart is apparently called Graphique 1 and according to the excel documentation ChartObjects is called on the worksheet objet. easy right?

However nothing seems to work :

from win32com import client
xl = client.Dispatch("Excel.Application")
xl.Visible = 1
workbook = xl.Workbooks.Open(r"D:\some\path\file.xls")
ws = workbook.Sheets("the sheet")   
    
#tried but did not work :
ws.ChartObjects("Graphique 1").Activate = True #Exception occured : no element with this name
ws.ChartObjects("Graphique 1").Activate = 1 #Exception occured : no element with this name
ws.ChartObjects(1).Activate = True #Exception occured : no message
ws.ChartObjects(1).Activate = 1 #Exception occured : no message
#in case it's 0ed indexed
ws.ChartObjects(0).Activate = True #Exception occured : no message
ws.ChartObjects(0).Activate = 1 #Exception occured : no message
print ws.ChartObjects("Graphique 1").SeriesCollection(1).Values #Exception occured : no element with this name
print ws.ChartObjects(1).SeriesCollection(1).Values #Exception occured : no message

Any ideas? Thanks.

Ps : I don't really now a lot of things about excel, I have 10 sheets, I supose that my chartObject is on the sheet were it's being draw

EDIT

weirdly a ChartObjects count of each sheet return 0, how can it be? I can see it with my own eyes

>>> for i in range(1,10):
    ws = workbook.Sheets(i)
    co = ws.ChartObjects()
    count = co.Count
    print count

    
0
0
0
0
0
0
0
0
0
Community
  • 1
  • 1
sliders_alpha
  • 2,276
  • 4
  • 33
  • 52

1 Answers1

2

All of these things should work, however, if .ChartObjects() does not work maybe try .Shapes

import win32com.client as win32com

xl = win32com.DispatchEx ("Excel.Application")
xl.Visible = True

wb = xl.Workbooks.Open(r"C:\Book2.xls")

print wb.Worksheets(1).Name
# Selecting ChartObjects on a worksheet
wb.Worksheets(1).ChartObjects(1).Activate()
wb.Worksheets(1).ChartObjects(1).Select()

# Selecting Shapes on a worksheet (which charts are shapes)
#wb.Worksheets(1).Shapes(1).Activate() # Will not work
wb.Worksheets(1).Shapes(1).Select()

# This should loop through all of the shapes
for shape in wb.Worksheets(1).Shapes:
    print shape.Name

# This should loop though all Chart objects 
for chartz in wb.Worksheets(1).ChartObjects():
    print chartz.Name   # Print the name of the chart

If these do not work then apparently your excel file does not recognize any shapes or charts. Try creating a new chart in that workbook and then re-run these commands to see if it shows up, if not, there may be a problem with your excel file.

Radical Edward
  • 5,234
  • 5
  • 21
  • 33