Creating PowerShell Automatic Variables from C#

Posted by Uros Calakovic on Stack Overflow See other posts from Stack Overflow or by Uros Calakovic
Published on 2011-01-02T21:59:24Z Indexed on 2011/01/02 22:53 UTC
Read the original article Hit count: 225

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about c#

Related posts about powershell