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)
