How do I change the size or position of my chart with Powershell

 
# <---- Start Code ---------------------------------------> 
$xl = New-Object -comobject Excel.Application
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False
# Open a workbook
$wb = $xl.workbooks.add() 
#Create Worksheets
$ws = $wb.Worksheets.Item(1) # Opens Excel and 3 empty Worksheets
1..8 | % { $ws.Cells.Item(1,$_) = $_ } # adds some data
1..8 | % { $ws.Cells.Item(2,$_) = 9-$_ } # adds some data
$range = $ws.range("a${xrow}:h$yrow") # sets the Data range we want to chart
# create and assign the chart to a variable
#$ch = $xl.charts.add() # This will open a new sheet
$ch = $ws.shapes.addChart().chart # This will put the Chart in the selected WorkSheet
$ch.chartType = 58
$ch.setSourceData($range)
$RngToCover = $ws.Range("D5:J19") # This is where we want the chart
$ChtOb = $ch.Parent # This selects the current Chart
$ChtOb.Top = $RngToCover.Top # This moves it up to row 5
$ChtOb.Left = $RngToCover.Left # and to column D 
$ChtOb.Height = $RngToCover.Height # resize This sets the height of your chart to Rows 5 - 19
$ChtOb.Width = $RngToCover.Width # resize This sets the width to Columns D - J
<------------- End Code --------------------------------------> 

Thanks to Marco Shaw and to this article he pointed me to:
Read more: Resizing and Moving Excel Charts with VBA http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html#ixzz0seQImNEL

Advertisements