Creating PowerShell Automatic Variables from C#
- by Uros Calakovic
I trying to make automatic variables available to Excel VBA (like ActiveSheet or ActiveCell) also available to PowerShell as 'automatic variables'. PowerShell engine is hosted in an Excel VSTO add-in and Excel.Application is available to it as Globals.ThisAddin.Application. I found this thread here on StackOverflow and started created PSVariable derived classes like:
public class ActiveCell : PSVariable
{
public ActiveCell(string name) : base(name) { }
public override object Value
{
get
{
return Globals.ThisAddIn.Application.ActiveCell;
}
}
}
public class ActiveSheet : PSVariable
{
public ActiveSheet(string name) : base(name) { }
public override object Value
{
get
{
return Globals.ThisAddIn.Application.ActiveSheet;
}
}
}
and adding their instances to the current POwerShell session:
runspace.SessionStateProxy.PSVariable.Set(new ActiveCell("ActiveCell"));
runspace.SessionStateProxy.PSVariable.Set(new ActiveSheet("ActiveSheet"));
This works and I am able to use those variables from PowerShell as $ActiveCell and $ActiveSheet (their value change as Excel active sheet or cell change). Then I read PSVariable documentation here and saw this:
"There is no established scenario for deriving from this class. To programmatically create a shell variable, create an instance of this class and set it by using the PSVariableIntrinsics class."
As I was deriving from PSVariable, I tried to use what was suggested:
PSVariable activeCell = new PSVariable("ActiveCell");
activeCell.Value = Globals.ThisAddIn.Application.ActiveCell;
runspace.SessionStateProxy.PSVariable.Set(activeCell);
Using this, $ActiveCell appears in my PowerShell session, but its value doesn't change as I change the active cell in Excel.
Is the above comment from PSVariable documentation something I should worry about, or I can continue creating PSVariable derived classes? Is there another way of making Excel globals available to PowerShell?
