powershell cannot add more than one legend entry (series) to an excel chart

Question

I have a problem adding more than one series to the seriescollection in excels chart object through powershell here is my code:

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

$excel = New-Object -comobject Excel.Application

$workbook = $excel.workbooks.add()

$datasheet  = $workbook.Worksheets.Item(2)
$chartsheet = $workbook.Worksheets.Item(1)

[datetime] $startDate  = "2012-11-29 00:00:00" 
[datetime] $finishDate = "2012-12-07 00:00:00"
[datetime] $dayCounter = $startDate

$startRow = 2
$startColumn = 2

$columnCounter = 2
$rowCounter = 2
while ($dayCounter -le $finishDate)
{
  $datasheet.Cells.Item($rowCounter, $columnCounter) = $dayCounter.ToShortDateString()
  $datasheet.Cells.Item($rowCounter+1, $columnCounter) = $columnCounter
  $datasheet.Cells.Item($rowCounter+2, $columnCounter) = 2 * $columnCounter
  $columnCounter++
  $dayCounter = $dayCounter.AddDays(1)
}

$datasheet.Range($rowCounter.ToString() + ":" + $rowCounter.ToString()).NumberFormat = "m/d/yyyy"

$excel.application.DisplayAlerts=$False
$chart = $chartsheet.Shapes.addChart().chart
$chart.hasTitle = $true
$chart.chartTitle.text = "Ramp Example"
$chartType = [Microsoft.Office.Interop.Excel.XlChartType]::xlLine
$chart.chartType = $chartType

$startCell = $datasheet.Cells.Item(3,2).Address($false,$false)
$endCell   = $datasheet.Cells.Item(3,10).Address($false,$false)

$startCell + ", " + $endCell

$datarange = $datasheet.Range($startCell, $endCell)
$chart.SetSourceData($datarange)
$chart.SeriesCollection(1).Name    = "First"
$chart.SeriesCollection(1).XValues = $datasheet.Range("B2", "J2")

$newSeries = $chart.SeriesCollection().NewSeries
$chart.SeriesCollection(2).Values  = $datasheet.Range("B4", "J4")
$chart.SeriesCollection(2).Name    = "Second"
$chart.SeriesCollection(2).XValues = $datasheet.Range("B2", "J2")


$excel.Visible = $True

THE ERRORS:

Exception calling "SeriesCollection" with "1" argument(s): "Invalid Parameter" At C:\localwork\tfs\OpenExcel.ps1:49 char:24 + $chart.SeriesCollection <<<< (2).Values = $datasheet.Range("B4", "J4") + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

Exception calling "SeriesCollection" with "1" argument(s): "Invalid Parameter" At C:\localwork\tfs\OpenExcel.ps1:50 char:24 + $chart.SeriesCollection <<<< (2).Name = "Second" + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

Exception calling "SeriesCollection" with "1" argument(s): "Invalid Parameter" At C:\localwork\tfs\OpenExcel.ps1:51 char:24 + $chart.SeriesCollection <<<< (2).XValues = $datasheet.Range("B2", "J2") + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

The question is; how do I get an extra entry into the SeriesCollection with powershell code?

Any help will be apprecitated

You can answer your own question. And even accept it (although you have to wait a while to do the accept). This is recommended practice, because it documents Q and A for posterity better than the comment, and it doesn't leave the question out there as unanswered. You might even get some reputation out of it, if people upvote your answer. — DWright
BTW, Please do answer you own question. I've noticed a distressing lack of quality for Powershell stuff on SO, vs. some other languages. One aspect of this is that I frequently run into questions where no answer was ever posted even though the comments indicate there was an answer of some type. — DWright

The Schwartz The Schwartz · Accepted Answer · 2013-01-13T21:54:17

Problem solved, had to call Invoke() on the newSeries - so:

$chart.SeriesCollection().NewSeries.Invoke()

And that's it, I guess there's some difference from calling the VBA in the macro I made in Excel to begin this development:

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("B3:P3")
ActiveChart.SeriesCollection(1).Name = "=""First"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet2!$B$4:$P$4"
ActiveChart.SeriesCollection(2).Name = "=""Second"""
ActiveChart.SeriesCollection(2).XValues = "=Sheet2!$B$2:$P$2"

To calling it in powershell, I have not found one good example on doing this adding of series dynamically to an Excel Chart on the WEB!

powershell cannot add more than one legend entry (series) to an excel chart

1 Answers