Error with Using Macro for XYScatter plots

A

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
 
H

Himani

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 !!!
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top