Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
graph troubles in VBA macros
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 Befo=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Troubles | Excel Discussion (Misc queries) | |||
Excel troubles | Excel Worksheet Functions | |||
COM Add-in Troubles | Excel Discussion (Misc queries) | |||
IF troubles | Excel Worksheet Functions | |||
Anyone have troubles running 2003 macros in 2000 version of Excel | Excel Discussion (Misc queries) |