1

I recorded the following macro :

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

However when I try to lauch it I get this error (translated from french):

Execution error '-2147024809 (80070057)'
There is no element with this name

How can this be? if there was no graph named this way I wouldn't have been able to record it. (yes I'm running it from the good sheet)

Thanks.

Gaffi
  • 4,307
  • 8
  • 43
  • 73
sliders_alpha
  • 2,276
  • 4
  • 33
  • 52

2 Answers2

2

Here's what it comes down to: Your chart is not an object on the sheet, it is the sheet.

So while you use ActiveSheet.ChartObjects("Graphique 1").Activate to start your code, there are no ChartObjects found in your sheet, because the sheet is the Chart. So here's how you get around it:

Dim CO As Variant
Set CO = ActiveSheet
CO.Axes(xlCategory).Select
CO.SeriesCollection(1).Values = "='Données'!$ET$68:$IJ$68"
CO.SeriesCollection(1).XValues = "='Données'!$ET$1:$IJ$1"

And this should work just fine. I noticed that when I looked at the chart tab, I couldn't get into any cells. This is not abnormal, but it is not the most common way (that I see) to create the chart. To verify, I added a watch on the ActiveSheet and saw that it was indeed a chart (of type Object/Graph2) with all the normal chart methods available to it.

From there, I just plugged in your code, converting to the CO variable (but yours should still work using ActiveSheet across the board), and ran with no errors.

As a side note, using ActiveSheet is not always effective, and it is generally better to explicitly call the sheet, i.e. Set CO = ThisWorkbook.Sheets("Rejets Techniques TGC")

Gaffi
  • 4,307
  • 8
  • 43
  • 73
  • ChartObjects Count return 0 (on all sheets) (but I can see the chart) – sliders_alpha May 17 '13 at 19:22
  • Yes, look I edited the question, I added a sheet selection before the macro. – sliders_alpha May 21 '13 at 08:23
  • @sliders_alpha Let me ask you another few things... Did you create the charts yourself, or did they come from another source? Is the .xls/xlsx/xlsm/etc file in the 2007 file format, or was it created in/for a different version of Excel? – Gaffi May 21 '13 at 11:20
  • It came from an other source, I can see that it's a "Excel 97-2003" xls files, does it matter? – sliders_alpha May 21 '13 at 11:22
  • Ok, however it contains confidential data, I'm going to write a script that is going to replace the 3 years of data by dummy data, I'll give it to you tomorow – sliders_alpha May 21 '13 at 13:45
  • well thank you. kind of weird that microsoft "record macro" wrote non-working code – sliders_alpha May 22 '13 at 13:03
  • @sliders_alpha Agreed. Sorry I can't provide more insight into that specific problem. Perhaps it's a bug? – Gaffi May 22 '13 at 13:35
0

1 - Check if the active sheet is the one that contaisn the chart. Or use the sheet name in code to run it from any sheet.

2 - Check if the good sheet contains the chart with exact "Graphique 1" name. Maybe there's an underline, like "Graphique_1", or no space "Graphique1"...

Daniel Möller
  • 84,878
  • 18
  • 192
  • 214
  • It does the same thing when using the name of the sheet, in properties the graph is name "graph2", using this name produce the same result – sliders_alpha May 17 '13 at 19:21
  • And to awnser more precisly, Graphique_1 and Graphique1 do the same thing. – sliders_alpha May 21 '13 at 08:25
  • To follow up on a different lead, @sliders_alpha: can you check the `Layout` tab under `Chart Tools` section (not sure if these are labeled the same in French) when you have `Graphique 1` selected in the workbook? In the `Properties` section, there should be a `Chart Name` box. Does this match the name you are using in VBA? – Gaffi May 21 '13 at 11:44
  • In Chart Tools>layout>properties box I can read "graph name : Rejets Techniques TGC". However this name produce the same result "no element with such a name" – sliders_alpha May 21 '13 at 12:03
  • Maybe the graph as the same name as the sheet? here look at this : http://s23.postimg.org/e3n9zeb6y/rejet_Tech_TGC.jpg – sliders_alpha May 21 '13 at 13:18