Pass Excel.Application from Excel 2007 Custom Task Pane to Hosted PowerShell

Uros Calakovic

Here is a test console application that works:

using System;
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);
        Console.WriteLine(app.Version);
        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 =