Passing a variable from Excel 2007 Custom Task Pane to Hosted PowerShell
- by Uros Calakovic
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)
