Make an Excel Chart with PowerShell

O

OldDog

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
 

My Computer

O

OldDog

On Aug 11, 3:44 pm, OldDog <[email protected]> wrote:

> 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
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
 

My Computer

K

Kiron

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
 

My Computer

Top