Vista - Make an Excel Chart with PowerShell

Hi,

As i am working through my project I keep hitting the wall with
Excel.
I need to take some data and create a stacked bar chart with it.


I created a macro in Excel to do this, but I would like to run it as
part of my PowerShell script.


Here is the Excell Macro:


Sub Macro3()
'
' Macro3 Macro
'


'
Range("A81:B82").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'2545'!$A$81:$B$82")
ActiveChart.ChartType = xlBarStacked
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.SeriesCollection(1).XValues = "='2545'!$A$80"
End Sub


Here is as far as I have gotten with PS;


$x = New-Object -comobject Excel.Application
$x.Visible = $True
$x.DisplayAlerts = $False
$y = $x.Workbooks.Add()
$z = $y.Worksheets.Item(1)
$xlBarStacked = 58

$range = $z.range("A81:B82").Select
$y.sheets.Shapes.AddChart.Select
<---- error is here !!!
$ActiveChart.SetSourceData Source:=Range("'2545'!$A$81:$B$82")
<---- error is here !!!
$ActiveChart.ChartType = $xlBarStacked
$ActiveChart.PlotArea.Select
$ActiveChart.SetSourceData
$ActiveSheet.ChartObjects("Chart 17").Activate
$ActiveChart.SeriesCollection(1).XValues = "='2545'!$A$80"


TIA


Olddog

On Aug 11, 3:44*pm, OldDog <mikef2...@xxxxxx> wrote:OK, After some fumbeling around I have got a chart to load with this
code;

$xlBarStacked = 58
$objRange = $x.range("A81:B82").Select
$objRange = $x.range("C81").Activate
$myChart = $x.Charts.Add()
$myChart.ChartType = $xlBarStacked

I will keep working at it and post my findings.

Olddog

There is no need to use Select() Method if you define the ranges.
Try this way:

$xlBarStacked = 58
$xl = new-object -c excel.application
$xl.visible = $true
$wb = $xl.workbooks.add()
$sh = $wb.sheets.item(1)
$range = $sh.range('a81:b82')
# create and assign the chart to a variable
$ch = $sh.shapes.addChart().chart
$ch.chartType = $xlBarStacked
$ch.setSourceData($range)
$ch.seriesCollection(1).xValues = $sh.range('a80')

# if the previous line is not what you expected, reset the data
$ch.setSourceData($range)

# excel has 48 chart styles, you can cycle through all
1..48 | % {$ch.chartStyle = $_; $xl.speech.speak("Style $_"); sleep 1}
$ch.chartStyle = 27 # <-- use the one you like

--
Kiron