VB script line graph
 

I am trying to create linechart through vb script

here is my data

06:00 200 05/01/2009
06:30 250 05/01/2009
07:00 300 05/01/2009
07:30 450 05/01/2009
....
....
.....
...

06:00 130 05/02/2009
06:30 220 05/02/2009
07:00 320 05/02/2009
07:30 450 05/02/2009
....
.....
......


This is my spreadsheet, to start with I am taking only two days

trying to draw line chart to display how pattern is

here is my code

ActiveWorkbook.Charts.Add
With ActiveWorkbook.ActiveChart
.ChartType = xlLineStacked
Dim i As Integer
Dim j As Integer
Dim rn As String
Dim rn2 As String
Dim TIT As String
Dim k As Integer
i = 2
k = 0
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C61"),
PlotBy :=xlColumns

' xlsAxisCategory = .Axes(, Excel.XlAxisGroup.xlPrimary)
Set objSheet = ActiveWorkbook.Sheets("Sheet1")

Do While objSheet.Range("A" & i).Value < ""
If objSheet.Range("A" & i).Value = "06:00:00 AM" Then
TIT = Format(objSheet.Range("c" & i), "Long Date")
j = i + 29
rn = "R" & j & "C2:" & "R" & i & "C2"
k = k + 1
ActiveWorkbook.ActiveChart.SeriesCollection.NewSer ies
ActiveWorkbook.ActiveChart.SeriesCollection(k).Nam e = TIT
ActiveWorkbook.ActiveChart.SeriesCollection(k).Val ues =
"=Sheet1!" & rn 'Sheets("sheet1").Range(rn)
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R32C1:R61C1"

End If


i = i + 1
Loop
.Refresh


what i am doing is, two series for two dates. only changing values for same
category (x) axis
I do manually i am getting graph they way I want, when use above script,
first series is correct but
for second series, it is ploting graph by combing both date values.

can any one help me to solve

Thanks
K

VB script line graph
 

Any help

Thanks


"Kalyan" wrote:

I am trying to create linechart through vb script

here is my data

06:00 200 05/01/2009
06:30 250 05/01/2009
07:00 300 05/01/2009
07:30 450 05/01/2009
...
...
....
..

06:00 130 05/02/2009
06:30 220 05/02/2009
07:00 320 05/02/2009
07:30 450 05/02/2009
...
....
.....


This is my spreadsheet, to start with I am taking only two days

trying to draw line chart to display how pattern is

here is my code

ActiveWorkbook.Charts.Add
With ActiveWorkbook.ActiveChart
.ChartType = xlLineStacked
Dim i As Integer
Dim j As Integer
Dim rn As String
Dim rn2 As String
Dim TIT As String
Dim k As Integer
i = 2
k = 0
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C61"),
PlotBy :=xlColumns

' xlsAxisCategory = .Axes(, Excel.XlAxisGroup.xlPrimary)
Set objSheet = ActiveWorkbook.Sheets("Sheet1")

Do While objSheet.Range("A" & i).Value < ""
If objSheet.Range("A" & i).Value = "06:00:00 AM" Then
TIT = Format(objSheet.Range("c" & i), "Long Date")
j = i + 29
rn = "R" & j & "C2:" & "R" & i & "C2"
k = k + 1
ActiveWorkbook.ActiveChart.SeriesCollection.NewSer ies
ActiveWorkbook.ActiveChart.SeriesCollection(k).Nam e = TIT
ActiveWorkbook.ActiveChart.SeriesCollection(k).Val ues =
"=Sheet1!" & rn 'Sheets("sheet1").Range(rn)
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R32C1:R61C1"

End If


i = i + 1
Loop
.Refresh


what i am doing is, two series for two dates. only changing values for same
category (x) axis
I do manually i am getting graph they way I want, when use above script,
first series is correct but
for second series, it is ploting graph by combing both date values.

can any one help me to solve

Thanks
K