Vista Tutorial - Problems creating trendlines in powershell-created excel chart

I'm trying to generate some simple linear regression charts in excel, driven by a powershell script. The only problem so far is that I a get an invocation failure in powershell when I attempt to generate Trendlines from the two SeriesCollections in the chart. I'm using powershell 1.0 (x64) and excel 2003. Any idea what's wrong the the script (below)?

ChDir "c:\conheap"
$xl = new-object -comobject excel.application
$xl.Visible = $true
$file = "c:\conheap\bcc_final.dat"
$xl.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$True,$False)
$wkb = $xl.workbooks.item(1)
$wks = $wkb.worksheets.item(1)
$wks.select()
$xlyscatter = -4169
$rng = $wks.range("A1:C21")
$rng.select()
$ac = $wkb.Charts.Add()
$ac.ChartType = -4169
$ac.setSourceData($rng)
$ac.seriesCollection(1).xValues=$wks.range("A1:A21")
$ac.seriesCollection(2).xValues=$wks.range("A1:A21")

$ac.SeriesCollection(1).select()
$ac.SeriesCollection(1).Trendlines.Add(-4132,0,0,0,$True,$True)

$ac.SeriesCollection(2).select()
$ac.SeriesCollection(2).Trendlines.add(-4132,0,0,0,$True,$True)


Output from powershell:
True
True
Method invocation failed because [System.Management.Automation.PSMethod]
doesn't contain a method named 'Add'.
At C:\conheap\bcc_excel.ps1:34 char:39
+ $ac.SeriesCollection(1).Trendlines.Add( <<<< -4132,0,0,0,$True,$True)
True
Method invocation failed because [System.Management.Automation.PSMethod]
doesn't contain a method named 'Add'.

At C:\conheap\bcc_excel.ps1:39 char:39
+ $ac.SeriesCollection(2).Trendlines.add( <<<< -4132,0,0,0,$True,$True)

Trendlines()'s Add Method signature is:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)

Need to add empty parenthesis to Trendlines though.

To call the method with all its arguments set the Order and Period to 2, they're the default anyway and 0 or $null won't work, but the Type could be set as xlPolynomial, you'd have to correct that like this:
# mind word wrap
$serie1Trend1 = $ac.SeriesCollection(1).Trendlines().Add(-4132,2,2,0,0,0,$True,$True,'Serie1Trend1')
$serie1Trend1.type = -4132

# or, create the trendline as xlLinear -default- and then
# adjust its properties
$serie1Trend1 = $ac.SeriesCollection(1).Trendlines().Add()
$serie1Trend1.DisplayEquation = $true
$serie1Trend1.DisplayRSquared = $true
$serie1Trend1.Name = 'myTL'

# another option
$serie1Trend1 = $ac.SeriesCollection(1).Trendlines().Add()
$serie1Trend1 | % {
$_.DisplayEquation = $true
$_.DisplayRSquared = $true
$_.Name = 'myTL'
}

--
Kiron

On Oct 12, 11:22*am, "Kiron" <Ki...@xxxxxx> wrote:Not that i doubt you, but I think you are doing some VooDoo here. If i
run this script up to the point of the error and then do a get
methedod, i get;

PS C:\files\xls> $ac.SeriesCollection(2).Trendlines | gm


TypeName: System.Management.Automation.PSMethod

Name MemberType Definition
---- ---------- ----------
Copy Method
System.Management.Automation.PSMemberInfo Copy()
Equals Method System.Boolean Equals(Object obj)
GetHashCode Method System.Int32 GetHashCode()
GetType Method System.Type GetType()
get_IsInstance Method System.Boolean get_IsInstance()
get_MemberType Method
System.Management.Automation.PSMemberTypes get_MemberType()
get_Name Method System.String get_Name()
get_OverloadDefinitions Method
System.Collections.ObjectModel.Collection`1[[System.String, mscorlib,
Version=2.0...
get_TypeNameOfValue Method System.String get_TypeNameOfValue()
get_Value Method System.Object get_Value()
Invoke Method System.Object Invoke(Params
Object[] arguments)
set_Value Method System.Void set_Value(Object value)
ToString Method System.String ToString()
IsInstance Property System.Boolean IsInstance {get;}
MemberType Property
System.Management.Automation.PSMemberTypes MemberType {get;}
Name Property System.String Name {get;}
OverloadDefinitions Property
System.Collections.ObjectModel.Collection`1[[System.String, mscorlib,
Version=2.0...
TypeNameOfValue Property System.String TypeNameOfValue
{get;}
Value Property System.Object Value {get;set;}

There is No Add mehtod there.

So how did you get that method?

OldDog

On Oct 12, 12:54*pm, OldDog <mikef2...@xxxxxx> wrote:And by the way, adding () after Trendlines seems to fix the code.

$ac.SeriesCollection(1).Trendlines().Add(-4132,0,0,0,$true,$True)

Thanks folks,

One small thing...the sixth arg in the Trendlines().Add() must be $False to avoid forcing the y-intercept to 0.

Or to be brutally explicit:

$ac.SeriesCollection(1).Trendlines().Add(-4132,4,0,0,0,$False,$True,$True,'Serie*1Trend1')

Perhaps stay with the plain Add().
This way, one has a "scripting" or
direct contact to each of the objects
and then set or change them!
A lot changed in Excel 2007
and really a lot in 2007 charting
(for charting, some say not in a
good way)!

But every new tech way hears this!

For 2007 (and higher versions)
start using Excel services and OpenXML
(just what the PowerShell people asked
for but few use and fewer support).

Which of course just means more .NET
knowledge for the PowerShell admin to
"obtain".

This is why, until 2007 knowledge is "obtained",
perhaps just use ChartSpace charting, one of
the four (charting, spreadsheet, data source,
and pivottable) OWC Office Web Components!