0

I read a thread here about adding more than one series at an Excel chart that helped me a lot, but I also need to add 3 ranges of information to a same line (series) on a chart.
My objective is getting information from another Excel file, move it to the file with the charts and add the information (which is stored as normal text on the first sheet) to the charts.
I manage to create a series with two ranges, using this:

$xlChartType=[Microsoft.Office.Interop.Excel.XLChartType] 
$XlLegendPosition=[Microsoft.Office.Interop.Excel.XlLegendPosition]

$chart=$WorksheetTool2.Shapes.AddChart().Chart
$chart.chartType=$xlChartType::xlLineMarkers 
$chart.SeriesCollection().NewSeries.Invoke() 
$chart.SeriesCollection(1).Values = $WorksheetTool1.Range("D10:D11","D20:D24") 
$chart.SeriesCollection(1).Name = "=""First""

but I need to add a third range, if I add a third range the error I'm getting is:

Exception getting "Range": "Cannot find an overload for "Range" and the argument count: "3"." At C:\Users\graciela_robert\Desktop\PerfTool\script.ps1:57 char:1 + $chart.SeriesCollection(1).Values = $WorksheetTool1.Range("D10:D11","D20:D24","D ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], GetValueInvocationException + FullyQualifiedErrorId : CatchFromBaseParameterizedPropertyAdapterGetValue

Does anyone knows how to add a third range into this chart?

Also, I'm trying to move the chart legend to the bottom by using 

$XlLegendPosition=[Microsoft.Office.Interop.Excel.XlLegendPosition]
$chart.Legend=$XlLegendPosition::xlLegendPositionBottom

But it doesn't work. I also tried just "#$chart.Legend=--4107" which I read here, but it also doesn't work. No errors, just nothing changes.

Sorry for the two questions! Thanks!

Community
  • 1
  • 1
Graci Robert
  • 3
  • 3
  • For the second question I would try `$chart.legend.position = 'xlLegendPositionBottom'` Which I found by looking up [Legend Members](https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.legend_members.aspx) and seeing the Position property, and looking at the options for it. – TheMadTechnician Jan 12 '16 at 21:40
  • For your first issue I would suggest doing `$chart.SeriesCollection(1).Add($WorksheetTool1.Range("D10:D11"))` and then repeating for the other two ranges. Your ranges don't seem to make sense though since they are different lengths. – TheMadTechnician Jan 13 '16 at 00:15
  • I need to pick up the data from the last 2 years, last 5 quarters and last 13 weeks, that's why the lengths are different :) I read the link you mentioned, but when I try to use it I get an exception: Exception setting "Position": "Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))" weird :( – Graci Robert Jan 13 '16 at 13:53

1 Answers1

0

To make an actual answer for this... The SeriesCollection method that you call returns a SeriesCollection collection. That collection has an object for each range on your chart that is pretty much defined by the Formula property. So let's suppose the following:enter image description here
For your chart we can establish all 4 lines with the following:

$chart.SeriesCollection().add($WorksheetTool1.Range("B2:E2")
$chart.SeriesCollection().add($WorksheetTool1.Range("B3:E3")
$chart.SeriesCollection().add($WorksheetTool1.Range("B4:E4")
$chart.SeriesCollection().add($WorksheetTool1.Range("I1:L1")

That puts lines on the chart for us. It doesn't put in a legend, and it doesn't fill in the dates at the bottom, but it puts in our lines and auto-generates the scale up the side. So, when we look at the SeriesCollection it gives us 4 objects, and we just need to do a little fixing on them. Now, as I mentioned before the Formula property on each of those holds all the info we need. Its laid out like:

=SERIES(<Series Title>,<Series Index Points>,<Range>,Index)

So to put some context here, the first object in our array should have the following Formula to make it display like we want it to (assuming your Sheet name is 'Tool1' as implied by your variable name):

$chart.SeriesCollection(1).Formula = '=SERIES(Tool1!$A$2,Tool1!$B$1:$E$1,Tool1!$B$2:$E$2,1)'

Then the rest of them, as appropriate...

$chart.SeriesCollection(2).Formula = '=SERIES(Tool1!$A$3,Tool1!$B$1:$E$1,Tool1!$B$3:$E$3,2)'
$chart.SeriesCollection(3).Formula = '=SERIES(Tool1!$A$4,Tool1!$B$1:$E$1,Tool1!$B$4:$E$4,3)'
$chart.SeriesCollection(4).Formula = '=SERIES(Tool1!$H$1,Tool1!$B$1:$E$1,Tool1!$I$1:$L$1,4)'

Now we have a chart showing all four series, with our legend, and labeled appropriately:
enter image description here

Edit: Ok, I see your example, and replicated it in Excel. Simple enough to do from what I could tell. Here's what I did. I opened Excel as a comobject in PowerShell and created a blank workbook. I made it visible, and added in some random names, and typed in the headers that were listed in your example chart. Then I populated data with some get-random goodness in PowerShell, and manually created the chart. Here's what I ended up with:enter image description here
So that's close enough to the chart that you gave as an example I'm thinking. So what does PowerShell show for the formulas? Here's what I've got:

=SERIES(Sheet1!$A$2,Sheet1!$B$1:$X$1,Sheet1!$B$2:$X$2,1)
=SERIES(Sheet1!$A$3,Sheet1!$B$1:$X$1,Sheet1!$B$3:$X$3,2)
=SERIES(Sheet1!$A$4,Sheet1!$B$1:$X$1,Sheet1!$B$4:$X$4,3)
=SERIES(Sheet1!$A$5,Sheet1!$B$1:$X$1,Sheet1!$B$5:$X$5,4)
=SERIES(Sheet1!$A$6,Sheet1!$B$1:$X$1,Sheet1!$B$6:$X$6,5)

The SeriesCollection() has 5 members, one for each person. They are formatted just as I had mentioned before.

TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56
  • Oh, it's nice to know that there is another way to do it using the =SERIES formula, but I don't think I was very clear on the problem I'm facing. I need to create a chart like this: [Chart](http://s29.postimg.org/44y2v45uv/chart.jpg) Sorry I can't format my comments correctly haha. Anyways, your way worked to set the first legends to the bottom (like FY15+FY16) but the legend is still on the right side of the chart and there is only one series of ranges, and I need three ranges for each series WITH the break between them – Graci Robert Jan 13 '16 at 14:03
  • I tried using like `$chart.SeriesCollection(2).Values = $WorksheetTool1.Range("E10:E11,E20:E24,E109:E121")` but, without the quotes it became an continuous line, but the line has to show the space between FY Quarters and Weeks otherwise the data doesn't makes sense. Here is how I tried with your formula: `$chart.SeriesCollection().NewSeries.Invoke() $chart.SeriesCollection(1).Formula = '=SERIES(Data!D5,Data!C10:C11,Data!D10:D11,1)'` and I tried adding another =series with the second info (quarters) but if overwrote the Year data – Graci Robert Jan 13 '16 at 14:11
  • You don't actually want 3 ranges, you want one range with breaks in it. I'll update my answer – TheMadTechnician Jan 13 '16 at 18:25
  • Thank you so much, @TheMadTechnician ! Thanks not only for the answer, but to help me understanding the =series formula! Really appreciate it :) – Graci Robert Jan 14 '16 at 16:24