I am testing PowerShell hosting using C#. Here is a console application that works:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
using
Microsoft.Office.Interop.Excel;
namespace ConsoleApplication3
{
class Program
{
static void Main()
{
Application app = new Application();
app.Visible = true;
app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Runspace runspace = RunspaceFactory.CreateRunspace();
runspace.Open();
runspace.SessionStateProxy.SetVariable("Application", app);
Pipeline pipeline = runspace.CreatePipeline("$Application");
Collection<PSObject> results = null;
try
{
results = pipeline.Invoke();
foreach (PSObject pob in results)
{
Console.WriteLine(pob);
}
}
catch (RuntimeException re)
{
Console.WriteLine(re.GetType().Name);
Console.WriteLine(re.Message);
}
}
}
}
I first create an Excel.Application instance and pass it to
the hosted PowerShell instance as a varible named $Application. This works and I can use this variable as if Excel.Application was created from within PowerShell.
I next created an Excel addin using VS 2008 and added a user control with two text boxes and a button to
the addin (the user control appears as a custom task pane when Excel starts).
The idea was this: when I click
the button a hosted PowerShell instance is created and I can pass to it
the current Excel.Application instance as a variable, just like in
the first sample, so I can use this variable to automate Excel from PowerShell (one text box would be used for input and
the other one for output. Here is
the code:
using System;
using System.Windows.Forms;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
using System.Collections.ObjectModel;
using
Microsoft.Office.Interop.Excel;
namespace POSHAddin
{
public partial class POSHControl : UserControl
{
public POSHControl()
{
InitializeComponent();
}
private void btnRun_Click(object sender, EventArgs e)
{
txtOutput.Clear();
Microsoft.Office.Interop.Excel.Application app =
Globals.ThisAddIn.Application;
Runspace runspace = RunspaceFactory.CreateRunspace();
runspace.Open();
runspace.SessionStateProxy.SetVariable("Application", app);
Pipeline pipeline = runspace.CreatePipeline(
"$Application | Get-Member | Out-String");
app.ActiveCell.Value2 = "Test";
Collection<PSObject> results = null;
try
{
results = pipeline.Invoke();
foreach (PSObject pob in results)
{
txtOutput.Text += pob.ToString() + "-";
}
}
catch (RuntimeException re)
{
txtOutput.Text += re.GetType().Name;
txtOutput.Text += re.Message;
}
}
}
}
The code is similar to
the first sample, except that
the current Excel.Application instance is available to
the addin via Globals.ThisAddIn.Application (VSTO generated) and I can see that it is really a
Microsoft.Office.Interop.Excel.Application instance because I can use things like app.ActiveCell.Value2 = "Test" (this actually puts
the text into
the active cell). But when I pass
the Excel.Application instance to
the PowerShell instance what gets there is an instance of System.__ComObject and I can't figure out how to cast it to Excel.Application. When I examine
the variable from PowerShell using $Application | Get-Member this is
the output I get in
the second text box:
TypeName: System.__ComObject
Name MemberType Definition
---- ---------- ----------
CreateObjRef Method System.Runtime.Remoting.ObjRef CreateObj...
Equals Method System.Boolean Equals(Object obj)
GetHashCode Method System.Int32 GetHashCode()
GetLifetimeService Method System.Object GetLifetimeService()
GetType Method System.Type GetType()
InitializeLifetimeService Method System.Object InitializeLifetimeService()
ToString Method System.String ToString()
My question is how can I pass an instance of
Microsoft.Office.Interop.Excel.Application from a VSTO generated Excel 2007 addin to a hosted PowerShell instance, so I can manipulate it from PowerShell?
(I have previously posted
the question in
the Microsoft C# forum without an answer)
