Passing a variable from Excel 2007 Custom Task Pane to Hosted PowerShell

Question

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)

Answer 1

My first thought is that the PowerShell runspace might not have the interop assembly loaded:

[void][Reflection.Assembly]::LoadWithPartialName('Microsoft.Office.Interop.Excel')

Do you get an error if you try an explicit cast?

$excel = [Microsoft.Office.Interop.Excel.Application] $Application

You might also be running into a threading issue if the object requires STA mode. With PowerShell v2, you might try this:

        Runspace runspace = RunspaceFactory.CreateRunspace();
        runspace.ThreadOptions = PSThreadOptions.UseCurrentThread;
        runspace.Open();
        runspace.SessionStateProxy.SetVariable("Application", app);

Or if UseCurrentThread doesn't work:

        Runspace runspace = RunspaceFactory.CreateRunspace();
        runspace.ApartmentState = System.Threading.ApartmentState.STA;
        runspace.ThreadOptions = PSThreadOptions.ReuseThread;
        runspace.Open();
        runspace.SessionStateProxy.SetVariable("Application", app);

You can find out more about using STA with PowerShell in this post.

Answer 2

It appears that the type you get back from Globals.ThisAddin.Application is a transparent/remoting proxy (System.Runtime.Remoting.Proxies.__TransparentProxy). Apparently PowerShell is having a hard time finding the type library info for it.

Answer 3

As keith-hill has pointed out, the problem appears to be that Powershell can't find type library info. One way of getting around it is to use the InvokeMember method to operate on the System.__ComObject instance. This way, you can manipulate the object directly. This post has a better explanation with examples, though using ADSI rather than Excel.