J
Jean-Marie BOUDRENGHIEN
Hello,
I'm having troubles with creating some graphs with an Excel VBA Macro.
I would like to create about 35 graphs from a set of data stored in a unique
datasheet. As the set of data can change (length, numbers, etc ..) I created
a macro to generate the graph starting from a template graph.
The macro runs correctly a few times but after a variable number of graph
(from 5 to 15, it depends on the weather), it crashes with :
Run-Time Error '-2147417848 (80010108)' : Methode 'XValues' of object
'series' failed
If i go in debug mode and repeat the error, I get :
Run-Time Error '1004' Unable to set the XValues property of the Series
class.
After this error, excel has always a strange behavior (unable to select
cells), and it always finishes by an excel crash.
I've tried many many things but nothing works :
.. I'm running Office XP SP2 under Windows 2000. It doesn't work neither on
office 2000 nor office 97 (win95).
.. I read the Kb article Q210684 about copying sheets . None of the
workaround worked
o Save and close the workbook after each graph <- not working
o Import the graph from a template file (sheets.add type:=template.xlt) <-
not working
o The codename looks ok (chart1, chart2, chart3, etc .)
.. I can't create a user-defined style as this worksheet will be spread on
many computers. Even more, the user-defined style can't hold all information
I need (logos, textbox, graph size, ..)
Here is what I do ..
startR is a variable which holds the row number of the dataset's beginning
startC is a variable which holds the colomn number of the dataset's
beginning
For i = 1 To nperfgraph
Sheets("ModelePerf").Copy Before:=Sheets(1)
Set NewGraph = Sheets(1)
NewGraph.Name = "Perf Wet-Dry (" & i & ")"
NewGraph.Axes(1).MinimumScale = Sheets("Data").Cells(startR,
StartC - 2).Value
NewGraph.Axes(1).MaximumScale = Sheets("Data").Cells(startR +
nperfpts, StartC - 2).Value
For j = 1 To nperfcurve
DoEvents
Call BuildSerie(NewGraph, startR, nperfpts, StartC - 2, StartC +
(j + ((i - 1) * nperfcurve)) - 1)
Next j
NewGraph.SeriesCollection(1).Delete
Set NewGraph = Nothing
DoEvents
Next i
Sub BuildSerie(NewGraph, Row, Npts, AxeXC, StartC)
Dim Line As Variant
Data = "=Data!"
Set serie = NewGraph.SeriesCollection.NewSeries
x = Data & "r" & Row & "c" & AxeXC & ":r" & Row + Npts & "c" & AxeXC
Valeur = Data & "r" & Row & "c" & StartC & ":r" & Row + Npts & "c" & StartC
nom = Data & "r" & Row - 2 & "c" & StartC
serie.XValues = x ?--------------------- Macro crashes here.
serie.Values = Valeur
serie.Name = nom
Set serie = Nothing
DoEvents
End Sub
Any help would be grately appreciate.
I'm sure that there is a way to workaround this; I couldn't believe that no
one has ever done an auto-generation graph macro.
Thanks,
Vincent
I'm having troubles with creating some graphs with an Excel VBA Macro.
I would like to create about 35 graphs from a set of data stored in a unique
datasheet. As the set of data can change (length, numbers, etc ..) I created
a macro to generate the graph starting from a template graph.
The macro runs correctly a few times but after a variable number of graph
(from 5 to 15, it depends on the weather), it crashes with :
Run-Time Error '-2147417848 (80010108)' : Methode 'XValues' of object
'series' failed
If i go in debug mode and repeat the error, I get :
Run-Time Error '1004' Unable to set the XValues property of the Series
class.
After this error, excel has always a strange behavior (unable to select
cells), and it always finishes by an excel crash.
I've tried many many things but nothing works :
.. I'm running Office XP SP2 under Windows 2000. It doesn't work neither on
office 2000 nor office 97 (win95).
.. I read the Kb article Q210684 about copying sheets . None of the
workaround worked
o Save and close the workbook after each graph <- not working
o Import the graph from a template file (sheets.add type:=template.xlt) <-
not working
o The codename looks ok (chart1, chart2, chart3, etc .)
.. I can't create a user-defined style as this worksheet will be spread on
many computers. Even more, the user-defined style can't hold all information
I need (logos, textbox, graph size, ..)
Here is what I do ..
startR is a variable which holds the row number of the dataset's beginning
startC is a variable which holds the colomn number of the dataset's
beginning
For i = 1 To nperfgraph
Sheets("ModelePerf").Copy Before:=Sheets(1)
Set NewGraph = Sheets(1)
NewGraph.Name = "Perf Wet-Dry (" & i & ")"
NewGraph.Axes(1).MinimumScale = Sheets("Data").Cells(startR,
StartC - 2).Value
NewGraph.Axes(1).MaximumScale = Sheets("Data").Cells(startR +
nperfpts, StartC - 2).Value
For j = 1 To nperfcurve
DoEvents
Call BuildSerie(NewGraph, startR, nperfpts, StartC - 2, StartC +
(j + ((i - 1) * nperfcurve)) - 1)
Next j
NewGraph.SeriesCollection(1).Delete
Set NewGraph = Nothing
DoEvents
Next i
Sub BuildSerie(NewGraph, Row, Npts, AxeXC, StartC)
Dim Line As Variant
Data = "=Data!"
Set serie = NewGraph.SeriesCollection.NewSeries
x = Data & "r" & Row & "c" & AxeXC & ":r" & Row + Npts & "c" & AxeXC
Valeur = Data & "r" & Row & "c" & StartC & ":r" & Row + Npts & "c" & StartC
nom = Data & "r" & Row - 2 & "c" & StartC
serie.XValues = x ?--------------------- Macro crashes here.
serie.Values = Valeur
serie.Name = nom
Set serie = Nothing
DoEvents
End Sub
Any help would be grately appreciate.
I'm sure that there is a way to workaround this; I couldn't believe that no
one has ever done an auto-generation graph macro.
Thanks,
Vincent