Click here to Skip to main content
15,887,485 members
Home / Discussions / C#
   

C#

 
AnswerRe: Listboxes etc. on top of video Pin
Not Active18-Oct-09 9:26
mentorNot Active18-Oct-09 9:26 
GeneralRe: Listboxes etc. on top of video Pin
lvq68418-Oct-09 22:04
lvq68418-Oct-09 22:04 
QuestionTrying to send email... Pin
JollyMansArt18-Oct-09 6:50
JollyMansArt18-Oct-09 6:50 
AnswerRe: Trying to send email... Pin
Manas Bhardwaj18-Oct-09 9:19
professionalManas Bhardwaj18-Oct-09 9:19 
AnswerRe: Trying to send email... Pin
Not Active18-Oct-09 9:25
mentorNot Active18-Oct-09 9:25 
Questioncheck the text box string dynamically with database field Pin
Mangesh Tomar18-Oct-09 6:22
Mangesh Tomar18-Oct-09 6:22 
AnswerRe: check the text box string dynamically with database field Pin
Luc Pattyn18-Oct-09 11:11
sitebuilderLuc Pattyn18-Oct-09 11:11 
QuestionPass Excel.Application from Excel 2007 Custom Task Pane to Hosted PowerShell Pin
Uros Calakovic18-Oct-09 4:07
Uros Calakovic18-Oct-09 4:07 
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 =
                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 test, 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 PowerShell, 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() 



Is there a way to 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 on StackOverflow[^], and the best answer I got was to use InvokeMember, which works, but is not what I'm after.)

Thank you in advance.

»Prosti mja u moemu nedoumeniu što ciniti! Razsuždaj: najdoh se u nuždi! Ne prolivaj slezi! Obresti li budu az i polk put k dalšoj blagozracnoj Denici? I moj dolgi život projde aki kratcje žitie. I kamo az pojdu ... gorkost smerti vižu ...«

QuestionSubReport in Win Form (RDLC) Pin
Abdul Rahman Hamidy18-Oct-09 3:20
Abdul Rahman Hamidy18-Oct-09 3:20 
AnswerRe: SubReport in Win Form (RDLC) Pin
Christopher Stratmann17-Apr-10 18:22
Christopher Stratmann17-Apr-10 18:22 
QuestionOpen RTF file in MS Word & WordPad Pin
Jassim Rahma18-Oct-09 3:18
Jassim Rahma18-Oct-09 3:18 
AnswerRe: Open RTF file in MS Word & WordPad Pin
0x3c018-Oct-09 3:34
0x3c018-Oct-09 3:34 
AnswerRe: Open RTF file in MS Word & WordPad Pin
DaveyM6918-Oct-09 3:35
professionalDaveyM6918-Oct-09 3:35 
AnswerRe: Open RTF file in MS Word & WordPad Pin
Luc Pattyn18-Oct-09 11:14
sitebuilderLuc Pattyn18-Oct-09 11:14 
QuestionGenerate sql select statement from another select statement Pin
AhmedMasum18-Oct-09 3:02
AhmedMasum18-Oct-09 3:02 
AnswerRe: Generate sql select statement from another select statement Pin
Not Active18-Oct-09 3:38
mentorNot Active18-Oct-09 3:38 
GeneralRe: Generate sql select statement from another select statement Pin
AhmedMasum18-Oct-09 15:33
AhmedMasum18-Oct-09 15:33 
GeneralRe: Generate sql select statement from another select statement Pin
Luc Pattyn18-Oct-09 16:14
sitebuilderLuc Pattyn18-Oct-09 16:14 
GeneralRe: Generate sql select statement from another select statement Pin
AhmedMasum18-Oct-09 17:09
AhmedMasum18-Oct-09 17:09 
GeneralRe: Generate sql select statement from another select statement Pin
Luc Pattyn18-Oct-09 18:04
sitebuilderLuc Pattyn18-Oct-09 18:04 
GeneralRe: Generate sql select statement from another select statement Pin
AhmedMasum18-Oct-09 18:41
AhmedMasum18-Oct-09 18:41 
GeneralRe: Generate sql select statement from another select statement Pin
Luc Pattyn19-Oct-09 2:08
sitebuilderLuc Pattyn19-Oct-09 2:08 
GeneralRe: Generate sql select statement from another select statement Pin
AhmedMasum19-Oct-09 4:28
AhmedMasum19-Oct-09 4:28 
GeneralRe: Generate sql select statement from another select statement Pin
Luc Pattyn19-Oct-09 4:38
sitebuilderLuc Pattyn19-Oct-09 4:38 
GeneralRe: Generate sql select statement from another select statement Pin
AhmedMasum19-Oct-09 19:22
AhmedMasum19-Oct-09 19:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.