Excel Legend Key

[JimmyFo]

Hi folks, I am exporting to excel via VBScript and I am trying to figure out how to set the legend - what looks wrong with this? The code for the chart is as follows, the resulting image is attached:

objWorksheet.Columns.AutoFit
Dim objRange : Set objRange = objWorksheet.Range("B2","B4")
objRange.Select
objWorkbook.Charts.Add
objWorkbook.ActiveChart.ChartType = 52
objWorkbook.ActiveChart.SetSourceData objRange, 2
objWorkbook.ActiveChart.Location 2,"Budget for 6"
objWorkbook.ActiveChart.HasTitle = True
objWorkbook.ActiveChart.chartTitle.text = "Budget for 6"
objWorkbook.activechart.HasDataTable = True
objWorkbook.activechart.DataTable.ShowLegendKey = True

What I'd like is for the legend where is says "Series 1" to say, "Capital", or some value from a range, or cell.

Can I do this?

Thanks,
James

 

[dm4ever]

What if you changed this line:

objWorkbook.ActiveChart.SetSourceData objRange, 2

to

objWorkbook.ActiveChart.SetSourceData objRange, 1

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[dm4ever]

Oh, and this line:
objWorkbook.ActiveChart.ChartType = 52

to

objWorkbook.ActiveChart.ChartType = 51

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid