Help Please !!!

[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")
End Sub


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

 

[Guest]

i think the error seems to be caused by not coding some data sources.

from your code, ActiveChart.SeriesCollection(1).XValues "='200648500DC820'!C1"

200648500DC820 is worksheet' name. but you didn't

FYI, plz refer to http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

 

[anu]

Thank you

 

[NickHK]

As you are using automation to control Excel, you can prevents problems by
always only using fully qualified references. Do not use Selection,
ActiveXXXX, Range etc. otherwise you may have difficulties in closing Excel.
due to remaining (unexpected) references.
Also, as you are using Early binding, you can benefit from Intellisense by
declaring your variables appropriately.

Dim xlObject as excel.application
Dim xlWB as excel.workbook

Set xlObject = New Excel.Application

'To open the selected excel file
Set xlWB = xlObject.Workbooks.Open(CommonDialog1.FileName)

with xlwb.worksheets(1) '<< change as required
.Range("A1:H1").ClearContents
'etc

NickHK