Error with Using Macro for XYScatter plots

[anu]

Hi all,
I am trying to create a XY plotter graph for my experiment using VB 6.
My excel sheet has x1,x2,x3 data in the 1,3,5 columns and y1,y2,y3
data in the 2,4,6 columns to plot my 3 series.The first row has the
parameter names. i recorded a macro in excel and tried using it in Vb6
to chart the graph.
This is my code:

Private Sub Command1_Click()

Dim xlObject
Dim xlWB
Set xlObject = New Excel.Application
'To open the selected excel file
Set xlWB = xlObject.Workbooks.Open(CommonDialog1.FileName)
Range("A1:H1").Select
Selection.ClearContents

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='200648500DC820'!C1"
ActiveChart.SeriesCollection(1).Values = "='200648500DC820'!C2"
ActiveChart.SeriesCollection(2).XValues = "='200648500DC820'!C3"
ActiveChart.SeriesCollection(2).Values = "='200648500DC820'!C4"
ActiveChart.SeriesCollection(3).XValues = "='200648500DC820'!C5"
ActiveChart.SeriesCollection(3).Values = "='200648500DC820'!C6"
ActiveChart.Location Where:=xlLocationAsObject,
Name:="200648500DC820"
ActiveWorkbook.Save
ActiveSheet.ChartObjects("Chart 1").Activate
xlObject.DisplayAlerts = True

'To close Excel

xlWB.Close
xlObject.Application.Quit
Set xlWB = Nothing
Set xlObject = Nothing



MsgBox ("Done")


It gives an error : Unable to set the Xvalues property of the series
class.
I have three series and dont want to give range..

Any help would be greatly appreciated.

Thanks
Anu


End Sub

 

[Guest]

Specify range of cells. See code attached.

Sub CreateGraph()

Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
' ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range("A1:B7"),
PlotBy:= _
' xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "sheet1!A1"
ActiveChart.SeriesCollection(1).Values = Sheet1.Range("A2:A5")
ActiveChart.SeriesCollection(2).XValues = "ActiveSheet!B1"
ActiveChart.SeriesCollection(2).Values = Sheet1.Range("B2:B5")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Hope this help !!!

 

[Jon Peltier]

I'm amazed VB6 got as far as the first .XValues line. Does VB6 even know
what this refers to:

Range("A1:H1").Select
Selection.ClearContents

Replace it by this or something similar:

xlObject.ActiveSheet.Range("A1:H1").ClearContents

Similarly, change

Charts.Add
ActiveChart.{blahblah}

to this

xlWB.Charts.Add
xlObject.ActiveChart.{blahblah}

or better, declare a chart variable

Dim cht As Excel.Chart

Set cht = xlWB.Charts.Add
cht.{blahblah}

Later in the code you have

ActiveWorkbook.Save

which should be changed to

xlWB.Save

The less you need to refer to ActiveChart, ActiveWorkbook, and the like,
especially while automating Excel from another app, the better your code
will run.

- Jon