Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with Using Macro for XYScatter plots
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 Whe=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with Using Macro for XYScatter plots
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 Whe=xlLocationAsObject, Name:="Sheet3" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub Hope this help !!! "anu" wrote: 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 Whe=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with Using Macro for XYScatter plots
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "anu" wrote in message ups.com... 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 Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error bars in scatter plots | Excel Discussion (Misc queries) | |||
how to prevent zero error message for logarithmic plots | Charts and Charting in Excel | |||
Erroneous Error? re 32,000 data plots in 2D chart | Charts and Charting in Excel | |||
How can you create Box Plots (Box-and-Whisker Plots) in Excel? | Charts and Charting in Excel | |||
Excel gives me line plots - I want scatter plots | Charts and Charting in Excel |