Vista - Powershell and Excel Trendlines

Part of my code creates a line chart from an excel document the adds a linear
reg trendline - no problem - I would line the trendline to be thicker and a
different color - prefer red. This is where I am stumped. Does anyone have
any ideas. A second unrelated question where can I find the chart type
enumerations?

$xlCellTypeLastCell=11
$xlContinuous=1
$xlMedium=-4138
$xlLine=4
$xl=New-Object -ComObject excel.application
$xl.visible=$True
$xl.application.DisplayAlerts=$False

$wb=$xl.Workbooks.Open("C:\DriveSpace_A\Chart.xls")
$sh=$wb.worksheets.item('Sheet1')
$used=$sh.usedRange
$lastCell=$used.SpecialCells($xlCellTypeLastCell)
$newRow=$lastCell.row + 1

# create chart
$sh=$wb.worksheets.item('Sheet1')
$x=("f2:f" + $newRow + "")
$x
$rangeB=$sh.range("$x")
$ch=$sh.Shapes.addChart().chart
$ch.hasLegend = $false
$ch.hasTitle = $true
$ch.chartTitle | gm
$ch.chartTitle.text = "SERSRV1 FREE SPACE"
$ch.chartType=$xlLine
$ch.setSourceData($rangeB),$xl.xlColumns
$tl=$ch.seriesCollection(1).Trendlines().add()

#$tl | % {
#$_.DisplayEquation=$True
#$_.DisplayRSquared=$True
#$_Name='MyTL'}

#$sh.SaveAs("C:\DriveSpace_A\sersrv1_Chart.xls")

# Colors
$vbBlack = 0x0
$vbRed = 0xFF
$vbGreen = 0xFF00
$vbYellow = 0xFFFF
$vbBlue = 0xFF0000
$vbMagenta = 0xFF00FF
$vbCyan = 0xFFFF00
$vbWhite = 0xFFFFFF

# Microsoft.Office.Interop.Excel.xlBorderWeight
$xlHairline = 1
$xlThin = 2
$xlThick = 4
$xlMedium = -4138

# change the values
$tl.Border.Weight = $xlThick
$tl.Border.Color = $vbBlue

--
Kiron

Mmm charting!

A great way to learn about charting,
while also learning about data parsing,
is via PowerShell's automation of the
data parsing tool, Microsoft's IIS's (local
or remote) data parser, Log Parser!

Why?

Because, besides data parsing, Log Parser
does "auto" charting. But Log Parser also
allows for "full" ChartSpace objects usage
charting (Excel uses ChartSpace too), via a
Log Parser's charting config file option!

Ah Log Parser,
truely a great tool,
that can be powershell automated,
for many things!

Remember, powershell.exe
is a Windows .NET admin's automation tool,
and is not a data parser tool and is not
a charting tool. But since PowerShell
is an automation tool, powershell.exe
can automate charting (or data parsing)
tools. Just like powershell.exe automates
IE or WMI or Regex or WinRM or any other
tool.

Anyway, now on to information about
ChartSpace's trendline charting (here
using OWC, the Office Web Components
charting, same as Excel, so all can play
along).
Mmm, let's do a trendline sample

# Define some needed ChartSpace constants.
$chChartTypeArea = 29
$chLineSolid = 7
$chLineRoundDot = 6
$chTrendlineTypeMovingAverage = 5
$chTrendlineTypeLinear = 1
$chTrendlineTypeExponential = 0
$chDimSeriesNames = 0
$chDataLiteral = -1
$chDimCategories = 1
$chDimValues = 2

# Try with number 10, 11, 12
$ChartSpace = New-Object -ComObject OWC10.ChartSpace

$theChart = $ChartSpace.Charts.Add()
$theChart.Type = $chChartTypeArea
$theChart.HasTitle = $true
$theChart.Title.Caption = "PowerShell ChartSpace Trendline Demo"

# The chart's values and categories.
$theValues = @(6324,8453,15324,13231,3280)
$theCategories = @("June","July","August","September","October")
$objSeries = $theChart.SeriesCollection.Add(0)
$objSeries.SetData($chDimSeriesNames, $chDataLiteral, "Money")
$objSeries.SetData($chDimCategories, $chDataLiteral, $theCategories)
$objSeries.SetData($chDimValues, $chDataLiteral, $theValues )

# Adjust the trendline properties.
$tl=$objSeries.Trendlines.Add()
$tl.IsDisplayingEquation = $true
$tl.IsDisplayingRSquared = $true
$tl.Type = $chTrendlineTypeExponential
$tl.Line.Color = "Red"
$tl.Line.Weight = 9
$tl.Line.DashStyle = $chLineRoundDot

# Save the chart.
$ChartSpace.ExportPicture("$pwd\testChart.gif", "gif", 620, 370 )

invoke-item testChart.gif

Exit
OWC ChartSpace charts that support trendlines:

XY, Area, Bar, Column, Line, Stock Market
and Bubble

For constants information
(varies a little from version to version):

Office Web Components Constants
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Microsoft Excel Constants [Excel 2003 VBA Language Reference]
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Enumerations - Excel 2007
http://msdn.microsoft.com/en-us/library/bb259478.aspx

For more help, ask any PowerShell user
or MVP or any ChartSpace user or MVP.

Also start looking at and using the much
newer Microsoft .NET Chart Control, a lot
of tools seem to be going towards them!

Have fun charting within the Windows .NET
admin's automation tool, powershell.exe.