charting independent XYScatter graphs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mb - 26 May 2008 23:54 GMT

I'm trying to create a XYScatter graph of 3 independent datasets from
within VB6 using OLE/Excel2000.

I'm using the KB147803 article as a basis, but am stuck and hope someone
can help.

The example in KB147803 seems to indicate that there can only be one set
of X-Values for the 3 XY Scatter Graph data sets.

The X-Value series is the first row of data in the sheet, and is called
out as a set of Category labels ('cwCategoryLabels=1'), and applies to
all three subsequent rows of Y-Value data.

In my case, I want to plot 3 independent sets of XY data, so I'd like to
provide a separate row of X-Value data for each row of Y-Value data.

I'm not seeing how I can pass this sort of a dataset to
objChartChartWizard...is there a way?

Thanks very much for any help!

Michael

Reply to this Message

Jon Peltier - 27 May 2008 00:39 GMT

Michael -

Visit this page:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

Pay attention to the sections on adding series:

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With
End Sub
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> I'm trying to create a XYScatter graph of 3 independent datasets from
> within VB6 using OLE/Excel2000.
[quoted text clipped - 18 lines]
>
> Michael

Reply to this Message

mb - 27 May 2008 01:13 GMT

Jon,

Thanks for the quick reply!

The example code you posted appears to be VBA: do you know if you can do
the same thing from within a Visual Basic 6 application?

I'm currently using Excel 2000 and can update it if necessary, but I"m
pretty stuck with VB6. Buying a VB6 add-on to enable this would be a viable
alternative.

thanks again,

Michael

>Michael -
>
[quoted text clipped - 40 lines]
>>
>> Michael

Reply to this Message

Jon Peltier - 27 May 2008 01:41 GMT

The code is nearly the same. The difference is that you have to qualify the
code in VB6 a bit more formally.

For instance, you need a variable pointing to the working instance of Excel.
Supposing the variable is xlApp, then the code I posted looks like this:

With xlApp.ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Jon,
>
[quoted text clipped - 56 lines]
>>>
>>> Michael

Reply to this Message

mb - 27 May 2008 14:05 GMT

Here's a snippet of my code:

OLE1.CreateEmbed "", "Excel.Chart.5"
Set objChart = OLE1.object.ActiveChart
Set objSheet = objChart.Parent.Worksheets(1)
Set objXL = objChart.Application

I tried substituting both "OLE1.object.Application" and "objXL" for xlApp in
your example:

With objXL.ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With

But I get the error: <Object variable or With block variable not set>

A watch on objXL shows it equal to "Microsoft Excel", but the ".ActiveChart"
provokes the error.

Any thoughts?

thanks,

Michael

>The code is nearly the same. The difference is that you have to qualify the
>code in VB6 a bit more formally.
[quoted text clipped - 75 lines]
>>>>
>>>> Michael

Reply to this Message

Jon Peltier - 27 May 2008 20:52 GMT

If your objChart is reliably defined, then:

With objChart.SeriesCollection.NewSeries

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Here's a snippet of my code:
>
[quoted text clipped - 110 lines]
>>>>>
>>>>> Michael

Reply to this Message