C# Aplpication talking to Excel Add-In

[Matthew Wieder]

I have the following requirements:
Build a stand-alone C# application that asks the user to click in a cell
in an Excel spreadsheet, and then displays the address of that cell in
the C# application. It seems simple enough, but the problem I'm
encountering is as follows:
In order for the user to select the cell from Excel, they must first
click once on the Excel window to give it focus and then their second
click is what changes the cell location. The transition from the
outside C# application to the user's cell of choice needs to be one
click, not two. This functionality exists if the display form is
written as an Excel Add-in, as in that case, Excel already is the
focused application and therefore no second clic is needed. I'm having
difficulty trying to come up with a hybrid approach such that I have an
add-in inside Excel to handle the single clicking duties, but it must be
able to pass the information back to an external C# application.
Can anyone help? (I'm posting to the Excel group also as this sort of
falls between the groups)
thanks!

[Peter Huang]

RE: C# Aplpication talking to Excel Add-In

Hi Matthew,

Thanks for posting in the community.

First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you wants to automation the Excel
from C#, also you wants to Excel to get the focus.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

Based on my test, if you automation a Excel from C#, the application will
be active window by default and you do not need to click the second times.
Also if the Excel application has lost focus, you may try to use the API
SetForegroundWi ndow to let the Excel got focus.

Here is my sample code you may have a try and let me know the result.
If you have any concern on this issue,please post here.

[DllImport("user 32.dll")]
private static extern int SetForegroundWi ndow(int hwnd);
Excel.Applicati on oXL=null;
private void button1_Click(o bject sender, System.EventArg s e)
{
Excel.Workbooks oWBS=null;
Excel.Workbook oWB=null;
oXL = new Excel.Applicati onClass();
oWBS = oXL.Workbooks;
oWB = oWBS.Add(Missin g.Value);
oWB.SheetSelect ionChange +=new
Microsoft.Offic e.Interop.Excel .WorkbookEvents _SheetSelection ChangeEventHand l
er(oWB_SheetSel ectionChange);

this.textBox1.T ext=oXL.ActiveC ell.get_Address (true,true,Exce l.XlReferenceSt y
le.xlA1 ,false,Missing. Value);
oXL.Visible=tru e;
}
private void oWB_SheetSelect ionChange(objec t Sh,
Microsoft.Offic e.Interop.Excel .Range Target)
{

this.textBox1.T ext=Target.get_ Address(true,tr ue,Excel.XlRefe renceStyle.xlA1
,false,Missing. Value);
}

private void button2_Click(o bject sender, System.EventArg s e)
{
int rt = SetForegroundWi ndow(oXL.Hwnd);
}


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Matthew Wieder]

Re: C# Aplpication talking to Excel Add-In

I don't think you've understood correctly. I have a Windows Form,
written in C#, that must dispay the value of the currently selected cell
in an Excel Worksheet. The form is set to always be on top, so that the
user can make changes to the currently selected cell, while the Windows
form is visible. The problem is that if the form has focus, and the
user wants to click on a cell in Excel, the first time they click on
Excel is "used" to give Excel focus since otherwise the external
application still has focus. Only a second click can actually move the
active cell.
If this is not clear, do the following. Open up Excel and open up Word
in a window next to it. Click in Word to make it the active
application. Then, click on a cell in Excel. Notice how the cell you
clicked on is not selected, but rather, the seelcted cell is whatever
was selected before you opened Word. You must click a second time now,
in order to select the cell you want.
However, if the C# Form is written as an Add-In, theen only one click is
needed. The requirement, is to preserve the "one click" behavior, while
having the application live otuside of Excel (not an add-in). If need
be, an add-in may be used, but the main application thread and the form
must not be part of the add-in.
I hope this clarifies the requirement.
Using something like "SetForgroundWi ndow" is not a valid solution since
it would require that any time, the user removes the mouse from the C#
Form, that Excel be given focus. We only want Excel to be given focus
if the user clicks on a cell in Excel, not simply for removing the
cursor from the C# form.
thanks,
-Matthew

Peter Huang wrote:[color=blue]
> Hi Matthew,
>
> Thanks for posting in the community.
>
> First of all, I would like to confirm my understanding of your issue.
> From your description, I understand that you wants to automation the Excel
> from C#, also you wants to Excel to get the focus.
> Have I fully understood you? If there is anything I misunderstood, please
> feel free to let me know.
>
> Based on my test, if you automation a Excel from C#, the application will
> be active window by default and you do not need to click the second times.
> Also if the Excel application has lost focus, you may try to use the API
> SetForegroundWi ndow to let the Excel got focus.
>
> Here is my sample code you may have a try and let me know the result.
> If you have any concern on this issue,please post here.
>
> [DllImport("user 32.dll")]
> private static extern int SetForegroundWi ndow(int hwnd);
> Excel.Applicati on oXL=null;
> private void button1_Click(o bject sender, System.EventArg s e)
> {
> Excel.Workbooks oWBS=null;
> Excel.Workbook oWB=null;
> oXL = new Excel.Applicati onClass();
> oWBS = oXL.Workbooks;
> oWB = oWBS.Add(Missin g.Value);
> oWB.SheetSelect ionChange +=new
> Microsoft.Offic e.Interop.Excel .WorkbookEvents _SheetSelection ChangeEventHand l
> er(oWB_SheetSel ectionChange);
>
> this.textBox1.T ext=oXL.ActiveC ell.get_Address (true,true,Exce l.XlReferenceSt y
> le.xlA1 ,false,Missing. Value);
> oXL.Visible=tru e;
> }
> private void oWB_SheetSelect ionChange(objec t Sh,
> Microsoft.Offic e.Interop.Excel .Range Target)
> {
>
> this.textBox1.T ext=Target.get_ Address(true,tr ue,Excel.XlRefe renceStyle.xlA1
> ,false,Missing. Value);
> }
>
> private void button2_Click(o bject sender, System.EventArg s e)
> {
> int rt = SetForegroundWi ndow(oXL.Hwnd);
> }
>
>
> Best regards,
>
> Peter Huang
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
> This posting is provided "AS IS" with no warranties, and confers no rights.
>[/color]

[Peter Huang]

Re: C# Aplpication talking to Excel Add-In

Hi Matthew,

Thanks for posting in the community.

I think this is the Excel's default behavior( click on the cell from
another application, will firstly activate the application). Also we should
not change the default behavior of Excel which may cause other problem.
Since you want the winform lie in another process, write an Addin will not
help in such case.

As a workaround,
You may consider embedding the workbook in the form itself.
Here is sample, you may take a look.
311765 SAMPLE: Visual C ActiveX Control for Hosting Office Documents in

http://support.microsoft.com/?id=311765

You may also try to host the excel window in winform

There are significant differences in the way the .NET Framework class
library supports managed windows and controls as compared with the Win32
API support for windows, or even the MFC/ATL class libraries.
Notwithstanding , a managed window will ultimately map to a native OS window
and can therefore be manipulated in the same way if necessary.

Technically, this includes windows created by Office applications. It
should be noted that it is generally not a good idea to interfere with the
management of an application's windows from outside the application. The
scope for conflict and unexpected behaviour is significant. From the
version table above, you'll see that the technique described here works for
Office 2003. While it works to a degree with earlier versions of Office,
there are a number of issues that make it impractical to use with earlier
versions.

Also the method has some drawback.

Given this warning, if you absolutely must perform some managed management
on an Office application's windows, this walkthrough will give you a
starting point. We'll build a managed user control to host Excel's main
window, and a managed Windows Forms test application.

Here is the mainly steps for you reference.
Excel User Control
1 First, create a new Windows class library, called ExcelControl. Add
references for the (Office 2003) PIAs for Excel. Add a new user control to
the project, using the filename ExcelControl.cs .

2. Declare some APIs.

[DllImport("user 32.dll")]
public static extern int FindWindowEx(
int hwndParent, int hwndChildAfter, string lpszClass,
int missing);

[DllImport("user 32.dll")]
static extern int SetParent(
int sheetWindow, int hWndNewParent);

[DllImport("user 32.dll")]
static extern bool MoveWindow(
int hWnd, int X, int Y, int nWidth, int nHeight,
bool bRepaint);

3 We'll expose three custom public methods from this control:

public void OpenWorkbook(st ring fileName){}
public void CloseWorkbook() {}
public void QuitExcel(){}

4 Declare some class fields. We'll need a reference to the Excel
Application, and to an Excel Workbook. Also an integer for the Excel window
handle, and 3 simple integers for window metrics (we'll use these to
determine where to position the Excel window):

private Excel.Applicati onClass xl = null;
private Excel.Workbook book = null;
public static int xlWindow;
private int captionHeight;
private int borderWidth;
private int borderHeight;

5 In the control's constructor, after the call to InitializeCompo nent,
assign initial values to the 3 simple integers from the system metrics:

captionHeight = SystemInformati on.CaptionHeigh t;
borderWidth = SystemInformati on.BorderSize.W idth;
borderHeight = SystemInformati on.BorderSize.H eight;

6 Now for the OpenWorkbook method. First, launch Excel, if we haven't
already:

if (xl == null)
{
xl = new Excel.Applicati onClass();
}

Then, find the Excel main window - this has a registered classname "XlMain":

if (xlWindow == 0)
{
xlWindow = FindWindowEx(0, 0, "XlMain", 0);
}

If we've found the Excel main window, make it a child of this control
window:

if (xlWindow != 0)
{
SetParent(xlWin dow, this.Handle.ToI nt32());

Then, try to open the requested workbook, make Excel visible, put it under
user control, and activate the current workbook:

try
{
object missing = Missing.Value;
book = xl.Workbooks.Op en(fileName,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing);
xl.Visible = true;
xl.UserControl = true;
book.Activate() ;

Then, move the Excel window, eliminating the caption area and borders.
Note: clipping the window in this way may seem like a hack, but we want to
keep our external manipulation of Excel's windows to a minimum, and this is
a less intrusive approach than any alternative.

MoveWindow(xlWi ndow,
-borderWidth,
-(captionHeight +borderHeight),
this.Bounds.Wid th +borderWidth *2,
this.Bounds.Hei ght +captionHeight +borderHeight *2,
true);

Note: the Excel commandbars will work OK, but the worksheet UI won't. Nor
will the scrollbars and sheet tabs. To fix these anomalies, we can simulate
some keyboard input:

SendKeys.Send(" ");
SendKeys.Send(" {ESC}");

Finally, set the focus back to the parent form:

this.Parent.Foc us();
}
catch (Exception ex)
{
MessageBox.Show (ex.Message);
}
}

7 Implementing the CloseWorkbook method is simply a matter of cleaning up
the automation object for the workbook:

if (book != null)
{
try
{
object missing = Missing.Value;
book.Close(miss ing, missing, missing);
Marshal.Release ComObject(book) ;
book = null;
}
catch (Exception ex)
{
MessageBox.Show (ex.Message);
}
}

8 Implementing the QuitExcel method is a little more involved. First, we
set the Excel window handle to null. Then, make sure we close any open
workbook before attempting to close Excel itself:

xlWindow = 0;
CloseWorkbook() ;

Then, if the Excel application reference is non-null, we can invoke Excel's
Quit method:

if (xl != null)
{
try
{
xl.Quit();

There is a race condition here, so we must make sure Excel has finished its
Quit cleanup operations before we attempt to release our final reference.
We can use the same technique as before: once the Excel main window has
been destroyed, we can assume cleanup has finished:

while (0 != FindWindowEx(0, 0, "XlMain", 0))
{
System.Threadin g.Thread.Sleep( 100);
}

Finally, cleanup our object reference and memory:

Marshal.Release ComObject(xl);
xl = null;
GC.Collect();
GC.WaitForPendi ngFinalizers();
}
catch (Exception ex)
{
MessageBox.Show (ex.Message);
}
}

9 Build the control project.
Windows Forms Test Application
10 Create a Windows Forms application, called HostExcelWindow . Put 3
buttons on the form to mirror the screenshot below - for invoking the
control's OpenWorkbook, CloseWorkbook and QutExcel methods. Get Click event
handlers for these 3 buttons. Also add a panel to the form. Also add an
OpenFileDialog to the form.

11 Add the ExcelControl user control assembly to the Toolbox. To do this,
right-click on the Toolbox and select "Customize Toolbox" or "Add/Remove
Items". Select the .NET Framework Components tab, and click the Browse
button. Navigate to the ExcelControl.dl l to add it to the list:



12 Then, drag+drop an instance of the ExcelControl from the Toolbox onto
the panel on the form. Set the control's Dock property to Fill, so that it
fills the panel.

13 Declare a couple of string fields in the form class for use in the
OpenFileDialog:

private string ExcelFilter =
"Workbooks (*.xls)|*.xls|" +
"Templates (*.xlt)|*.xlt|" +
"Addins (*.xla)|*.xla|" +
"All files (*.*)|*.*" ;
private string testFileName = "HostExcelWindo w.xls";

14 In the form's constructor, after the call to InitializeCompo nent, work
out the path to the sample test workbook, and setup the OpenFileDialog
properties:

string currentLocation = System.Environm ent.CurrentDire ctory;
string currentPath = currentLocation .Substring(
0, currentLocation .IndexOf(@"bin\ Debug"));

openFile.Initia lDirectory = currentPath;
openFile.Filter = ExcelFilter;
openFile.Restor eDirectory = true;
openFile.Title = "Open Excel File";
openFile.Filter Index = 0;
openFile.FileNa me = Path.Combine(cu rrentPath, testFileName);

15 In the button Click handler for the "Open Book" button, show the
OpenFileDialog, to allow the user to choose a workbook to open. Then open
the workbook using the control's exposed OpenWorkbook method:

if (openFile.ShowD ialog() == DialogResult.OK )
{
try
{
excelControl1.O penWorkbook(ope nFile.FileName) ;
cmdOpenBook.Ena bled = false;
cmdCloseBook.En abled = true;
cmdQuitExcel.En abled = true;
}
catch (Exception ex)
{
MessageBox.Show (ex.Message);
}
}

16 In the Click handler for the "Close Book" button, invoke the control's
CloseWorkbook method:

try
{
excelControl1.C loseWorkbook();
cmdCloseBook.En abled = false;
cmdOpenBook.Ena bled = true;
}
catch (Exception ex)
{
MessageBox.Show (ex.Message);
}

17 Add a new method to the form to invoke the control's QuitExcel method.
(We'll call this in two places):

private void QuitExcel()
{
excelControl1.Q uitExcel();
cmdQuitExcel.En abled = false;
cmdOpenBook.Ena bled = true;
cmdCloseBook.En abled = false;
}

18 The first place to call this method is the Click event handler for the
"Quit Excel" button; and the second place is in the Closing event handler
for the form itself (add this event handler using the events list in the
properties pane).

19 Build and test:

20 Note: there are several race conditions in this system. To make the
Excel control robust, we should hook events such as WorkbookBeforeC lose,
WorkBookOpen, etc.


If you have any concern on this issue, please post here.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Matthew Wieder]

Re: C# Aplpication talking to Excel Add-In

This solution seems problematic for a number of reasons, one being that
it would seem that for any interaction that I would like the application
to have with Excel, I need to write a method in the "middle layer" that
gets exposed to the application to do what I need.
I am working on a solution which is more favorable; to have the Windows
application talk to an Excel add-in, and tell the add-in to display a
form hosted in a stand-alone dll. This way, we have the benefit of the
form being hosted in Excel so it solves the clicking issue, and also the
benefit of the application existing outside of Excel.
To do this, I am using remoting, but have come to the following issue:
I have setup a remoting object which creates an instance of the form and
exposes a method to display it. I then have the remoting server (which
is inside the Excel add-in) create this remoting object and expose it
via RemotingService s.Marshal. The application then gets a proxy to this
object via RemotingService s.Connect, and calls the method to show the
form. All this works well as expected, and when I click on that form
and then click in Excel, I get the single click behavior. The problem is
that the DocEvents_Selec tionChangeEvent Handler which that form hooked
into in order to display the address of the currently selected cell
seems to stop firing once the application calls the show method on the
object. Further testing has shown that if the server calls the show
method on the object, then the event works fine, and even after the
application gets the proxy to the object the event continues to work,
but the moment the application calls the show method via the proxy, the
event stops being received by the form.
Can anyone help me out with this?
thanks!

Peter Huang wrote:[color=blue]
> Hi Matthew,
>
> Thanks for posting in the community.
>
> I think this is the Excel's default behavior( click on the cell from
> another application, will firstly activate the application). Also we should
> not change the default behavior of Excel which may cause other problem.
> Since you want the winform lie in another process, write an Addin will not
> help in such case.
>
> As a workaround,
> You may consider embedding the workbook in the form itself.
> Here is sample, you may take a look.
> 311765 SAMPLE: Visual C ActiveX Control for Hosting Office Documents in
> http://support.microsoft.com/?id=311765
>
> You may also try to host the excel window in winform
>
> There are significant differences in the way the .NET Framework class
> library supports managed windows and controls as compared with the Win32
> API support for windows, or even the MFC/ATL class libraries.
> Notwithstanding , a managed window will ultimately map to a native OS window
> and can therefore be manipulated in the same way if necessary.
>
> Technically, this includes windows created by Office applications. It
> should be noted that it is generally not a good idea to interfere with the
> management of an application's windows from outside the application. The
> scope for conflict and unexpected behaviour is significant. From the
> version table above, you'll see that the technique described here works for
> Office 2003. While it works to a degree with earlier versions of Office,
> there are a number of issues that make it impractical to use with earlier
> versions.
>
> Also the method has some drawback.
>
> Given this warning, if you absolutely must perform some managed management
> on an Office application's windows, this walkthrough will give you a
> starting point. We'll build a managed user control to host Excel's main
> window, and a managed Windows Forms test application.
>
> Here is the mainly steps for you reference.
> Excel User Control
> 1 First, create a new Windows class library, called ExcelControl. Add
> references for the (Office 2003) PIAs for Excel. Add a new user control to
> the project, using the filename ExcelControl.cs .
>
> 2. Declare some APIs.
>
> [DllImport("user 32.dll")]
> public static extern int FindWindowEx(
> int hwndParent, int hwndChildAfter, string lpszClass,
> int missing);
>
> [DllImport("user 32.dll")]
> static extern int SetParent(
> int sheetWindow, int hWndNewParent);
>
> [DllImport("user 32.dll")]
> static extern bool MoveWindow(
> int hWnd, int X, int Y, int nWidth, int nHeight,
> bool bRepaint);
>
> 3 We'll expose three custom public methods from this control:
>
> public void OpenWorkbook(st ring fileName){}
> public void CloseWorkbook() {}
> public void QuitExcel(){}
>
> 4 Declare some class fields. We'll need a reference to the Excel
> Application, and to an Excel Workbook. Also an integer for the Excel window
> handle, and 3 simple integers for window metrics (we'll use these to
> determine where to position the Excel window):
>
> private Excel.Applicati onClass xl = null;
> private Excel.Workbook book = null;
> public static int xlWindow;
> private int captionHeight;
> private int borderWidth;
> private int borderHeight;
>
> 5 In the control's constructor, after the call to InitializeCompo nent,
> assign initial values to the 3 simple integers from the system metrics:
>
> captionHeight = SystemInformati on.CaptionHeigh t;
> borderWidth = SystemInformati on.BorderSize.W idth;
> borderHeight = SystemInformati on.BorderSize.H eight;
>
> 6 Now for the OpenWorkbook method. First, launch Excel, if we haven't
> already:
>
> if (xl == null)
> {
> xl = new Excel.Applicati onClass();
> }
>
> Then, find the Excel main window - this has a registered classname "XlMain":
>
> if (xlWindow == 0)
> {
> xlWindow = FindWindowEx(0, 0, "XlMain", 0);
> }
>
> If we've found the Excel main window, make it a child of this control
> window:
>
> if (xlWindow != 0)
> {
> SetParent(xlWin dow, this.Handle.ToI nt32());
>
> Then, try to open the requested workbook, make Excel visible, put it under
> user control, and activate the current workbook:
>
> try
> {
> object missing = Missing.Value;
> book = xl.Workbooks.Op en(fileName,
> missing, missing, missing, missing, missing,
> missing, missing, missing, missing, missing,
> missing, missing, missing, missing);
> xl.Visible = true;
> xl.UserControl = true;
> book.Activate() ;
>
> Then, move the Excel window, eliminating the caption area and borders.
> Note: clipping the window in this way may seem like a hack, but we want to
> keep our external manipulation of Excel's windows to a minimum, and this is
> a less intrusive approach than any alternative.
>
> MoveWindow(xlWi ndow,
> -borderWidth,
> -(captionHeight +borderHeight),
> this.Bounds.Wid th +borderWidth *2,
> this.Bounds.Hei ght +captionHeight +borderHeight *2,
> true);
>
> Note: the Excel commandbars will work OK, but the worksheet UI won't. Nor
> will the scrollbars and sheet tabs. To fix these anomalies, we can simulate
> some keyboard input:
>
> SendKeys.Send(" ");
> SendKeys.Send(" {ESC}");
>
> Finally, set the focus back to the parent form:
>
> this.Parent.Foc us();
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 7 Implementing the CloseWorkbook method is simply a matter of cleaning up
> the automation object for the workbook:
>
> if (book != null)
> {
> try
> {
> object missing = Missing.Value;
> book.Close(miss ing, missing, missing);
> Marshal.Release ComObject(book) ;
> book = null;
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 8 Implementing the QuitExcel method is a little more involved. First, we
> set the Excel window handle to null. Then, make sure we close any open
> workbook before attempting to close Excel itself:
>
> xlWindow = 0;
> CloseWorkbook() ;
>
> Then, if the Excel application reference is non-null, we can invoke Excel's
> Quit method:
>
> if (xl != null)
> {
> try
> {
> xl.Quit();
>
> There is a race condition here, so we must make sure Excel has finished its
> Quit cleanup operations before we attempt to release our final reference.
> We can use the same technique as before: once the Excel main window has
> been destroyed, we can assume cleanup has finished:
>
> while (0 != FindWindowEx(0, 0, "XlMain", 0))
> {
> System.Threadin g.Thread.Sleep( 100);
> }
>
> Finally, cleanup our object reference and memory:
>
> Marshal.Release ComObject(xl);
> xl = null;
> GC.Collect();
> GC.WaitForPendi ngFinalizers();
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 9 Build the control project.
> Windows Forms Test Application
> 10 Create a Windows Forms application, called HostExcelWindow . Put 3
> buttons on the form to mirror the screenshot below - for invoking the
> control's OpenWorkbook, CloseWorkbook and QutExcel methods. Get Click event
> handlers for these 3 buttons. Also add a panel to the form. Also add an
> OpenFileDialog to the form.
>
> 11 Add the ExcelControl user control assembly to the Toolbox. To do this,
> right-click on the Toolbox and select "Customize Toolbox" or "Add/Remove
> Items". Select the .NET Framework Components tab, and click the Browse
> button. Navigate to the ExcelControl.dl l to add it to the list:
>
>
>
> 12 Then, drag+drop an instance of the ExcelControl from the Toolbox onto
> the panel on the form. Set the control's Dock property to Fill, so that it
> fills the panel.
>
> 13 Declare a couple of string fields in the form class for use in the
> OpenFileDialog:
>
> private string ExcelFilter =
> "Workbooks (*.xls)|*.xls|" +
> "Templates (*.xlt)|*.xlt|" +
> "Addins (*.xla)|*.xla|" +
> "All files (*.*)|*.*" ;
> private string testFileName = "HostExcelWindo w.xls";
>
> 14 In the form's constructor, after the call to InitializeCompo nent, work
> out the path to the sample test workbook, and setup the OpenFileDialog
> properties:
>
> string currentLocation = System.Environm ent.CurrentDire ctory;
> string currentPath = currentLocation .Substring(
> 0, currentLocation .IndexOf(@"bin\ Debug"));
>
> openFile.Initia lDirectory = currentPath;
> openFile.Filter = ExcelFilter;
> openFile.Restor eDirectory = true;
> openFile.Title = "Open Excel File";
> openFile.Filter Index = 0;
> openFile.FileNa me = Path.Combine(cu rrentPath, testFileName);
>
> 15 In the button Click handler for the "Open Book" button, show the
> OpenFileDialog, to allow the user to choose a workbook to open. Then open
> the workbook using the control's exposed OpenWorkbook method:
>
> if (openFile.ShowD ialog() == DialogResult.OK )
> {
> try
> {
> excelControl1.O penWorkbook(ope nFile.FileName) ;
> cmdOpenBook.Ena bled = false;
> cmdCloseBook.En abled = true;
> cmdQuitExcel.En abled = true;
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 16 In the Click handler for the "Close Book" button, invoke the control's
> CloseWorkbook method:
>
> try
> {
> excelControl1.C loseWorkbook();
> cmdCloseBook.En abled = false;
> cmdOpenBook.Ena bled = true;
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
>
> 17 Add a new method to the form to invoke the control's QuitExcel method.
> (We'll call this in two places):
>
> private void QuitExcel()
> {
> excelControl1.Q uitExcel();
> cmdQuitExcel.En abled = false;
> cmdOpenBook.Ena bled = true;
> cmdCloseBook.En abled = false;
> }
>
> 18 The first place to call this method is the Click event handler for the
> "Quit Excel" button; and the second place is in the Closing event handler
> for the form itself (add this event handler using the events list in the
> properties pane).
>
> 19 Build and test:
>
> 20 Note: there are several race conditions in this system. To make the
> Excel control robust, we should hook events such as WorkbookBeforeC lose,
> WorkBookOpen, etc.
>
>
> If you have any concern on this issue, please post here.
>
>
> Best regards,
>
> Peter Huang
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
> This posting is provided "AS IS" with no warranties, and confers no rights.
>[/color]

[Matthew Wieder]

Re: C# Aplpication talking to Excel Add-In

Also, in my testing, this problem: "Note: the Excel commandbars will
work OK, but the worksheet UI won't. Nor will the scrollbars and sheet
tabs. To fix these anomalies, we can simulate some keyboard input:" is
only solved by your workaround "SendKeys.Send( " ");
SendKeys.Send(" {ESC}");" about half the time.
thanks,
-Matthew

Peter Huang wrote:[color=blue]
> Hi Matthew,
>
> Thanks for posting in the community.
>
> I think this is the Excel's default behavior( click on the cell from
> another application, will firstly activate the application). Also we should
> not change the default behavior of Excel which may cause other problem.
> Since you want the winform lie in another process, write an Addin will not
> help in such case.
>
> As a workaround,
> You may consider embedding the workbook in the form itself.
> Here is sample, you may take a look.
> 311765 SAMPLE: Visual C ActiveX Control for Hosting Office Documents in
> http://support.microsoft.com/?id=311765
>
> You may also try to host the excel window in winform
>
> There are significant differences in the way the .NET Framework class
> library supports managed windows and controls as compared with the Win32
> API support for windows, or even the MFC/ATL class libraries.
> Notwithstanding , a managed window will ultimately map to a native OS window
> and can therefore be manipulated in the same way if necessary.
>
> Technically, this includes windows created by Office applications. It
> should be noted that it is generally not a good idea to interfere with the
> management of an application's windows from outside the application. The
> scope for conflict and unexpected behaviour is significant. From the
> version table above, you'll see that the technique described here works for
> Office 2003. While it works to a degree with earlier versions of Office,
> there are a number of issues that make it impractical to use with earlier
> versions.
>
> Also the method has some drawback.
>
> Given this warning, if you absolutely must perform some managed management
> on an Office application's windows, this walkthrough will give you a
> starting point. We'll build a managed user control to host Excel's main
> window, and a managed Windows Forms test application.
>
> Here is the mainly steps for you reference.
> Excel User Control
> 1 First, create a new Windows class library, called ExcelControl. Add
> references for the (Office 2003) PIAs for Excel. Add a new user control to
> the project, using the filename ExcelControl.cs .
>
> 2. Declare some APIs.
>
> [DllImport("user 32.dll")]
> public static extern int FindWindowEx(
> int hwndParent, int hwndChildAfter, string lpszClass,
> int missing);
>
> [DllImport("user 32.dll")]
> static extern int SetParent(
> int sheetWindow, int hWndNewParent);
>
> [DllImport("user 32.dll")]
> static extern bool MoveWindow(
> int hWnd, int X, int Y, int nWidth, int nHeight,
> bool bRepaint);
>
> 3 We'll expose three custom public methods from this control:
>
> public void OpenWorkbook(st ring fileName){}
> public void CloseWorkbook() {}
> public void QuitExcel(){}
>
> 4 Declare some class fields. We'll need a reference to the Excel
> Application, and to an Excel Workbook. Also an integer for the Excel window
> handle, and 3 simple integers for window metrics (we'll use these to
> determine where to position the Excel window):
>
> private Excel.Applicati onClass xl = null;
> private Excel.Workbook book = null;
> public static int xlWindow;
> private int captionHeight;
> private int borderWidth;
> private int borderHeight;
>
> 5 In the control's constructor, after the call to InitializeCompo nent,
> assign initial values to the 3 simple integers from the system metrics:
>
> captionHeight = SystemInformati on.CaptionHeigh t;
> borderWidth = SystemInformati on.BorderSize.W idth;
> borderHeight = SystemInformati on.BorderSize.H eight;
>
> 6 Now for the OpenWorkbook method. First, launch Excel, if we haven't
> already:
>
> if (xl == null)
> {
> xl = new Excel.Applicati onClass();
> }
>
> Then, find the Excel main window - this has a registered classname "XlMain":
>
> if (xlWindow == 0)
> {
> xlWindow = FindWindowEx(0, 0, "XlMain", 0);
> }
>
> If we've found the Excel main window, make it a child of this control
> window:
>
> if (xlWindow != 0)
> {
> SetParent(xlWin dow, this.Handle.ToI nt32());
>
> Then, try to open the requested workbook, make Excel visible, put it under
> user control, and activate the current workbook:
>
> try
> {
> object missing = Missing.Value;
> book = xl.Workbooks.Op en(fileName,
> missing, missing, missing, missing, missing,
> missing, missing, missing, missing, missing,
> missing, missing, missing, missing);
> xl.Visible = true;
> xl.UserControl = true;
> book.Activate() ;
>
> Then, move the Excel window, eliminating the caption area and borders.
> Note: clipping the window in this way may seem like a hack, but we want to
> keep our external manipulation of Excel's windows to a minimum, and this is
> a less intrusive approach than any alternative.
>
> MoveWindow(xlWi ndow,
> -borderWidth,
> -(captionHeight +borderHeight),
> this.Bounds.Wid th +borderWidth *2,
> this.Bounds.Hei ght +captionHeight +borderHeight *2,
> true);
>
> Note: the Excel commandbars will work OK, but the worksheet UI won't. Nor
> will the scrollbars and sheet tabs. To fix these anomalies, we can simulate
> some keyboard input:
>
> SendKeys.Send(" ");
> SendKeys.Send(" {ESC}");
>
> Finally, set the focus back to the parent form:
>
> this.Parent.Foc us();
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 7 Implementing the CloseWorkbook method is simply a matter of cleaning up
> the automation object for the workbook:
>
> if (book != null)
> {
> try
> {
> object missing = Missing.Value;
> book.Close(miss ing, missing, missing);
> Marshal.Release ComObject(book) ;
> book = null;
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 8 Implementing the QuitExcel method is a little more involved. First, we
> set the Excel window handle to null. Then, make sure we close any open
> workbook before attempting to close Excel itself:
>
> xlWindow = 0;
> CloseWorkbook() ;
>
> Then, if the Excel application reference is non-null, we can invoke Excel's
> Quit method:
>
> if (xl != null)
> {
> try
> {
> xl.Quit();
>
> There is a race condition here, so we must make sure Excel has finished its
> Quit cleanup operations before we attempt to release our final reference.
> We can use the same technique as before: once the Excel main window has
> been destroyed, we can assume cleanup has finished:
>
> while (0 != FindWindowEx(0, 0, "XlMain", 0))
> {
> System.Threadin g.Thread.Sleep( 100);
> }
>
> Finally, cleanup our object reference and memory:
>
> Marshal.Release ComObject(xl);
> xl = null;
> GC.Collect();
> GC.WaitForPendi ngFinalizers();
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 9 Build the control project.
> Windows Forms Test Application
> 10 Create a Windows Forms application, called HostExcelWindow . Put 3
> buttons on the form to mirror the screenshot below - for invoking the
> control's OpenWorkbook, CloseWorkbook and QutExcel methods. Get Click event
> handlers for these 3 buttons. Also add a panel to the form. Also add an
> OpenFileDialog to the form.
>
> 11 Add the ExcelControl user control assembly to the Toolbox. To do this,
> right-click on the Toolbox and select "Customize Toolbox" or "Add/Remove
> Items". Select the .NET Framework Components tab, and click the Browse
> button. Navigate to the ExcelControl.dl l to add it to the list:
>
>
>
> 12 Then, drag+drop an instance of the ExcelControl from the Toolbox onto
> the panel on the form. Set the control's Dock property to Fill, so that it
> fills the panel.
>
> 13 Declare a couple of string fields in the form class for use in the
> OpenFileDialog:
>
> private string ExcelFilter =
> "Workbooks (*.xls)|*.xls|" +
> "Templates (*.xlt)|*.xlt|" +
> "Addins (*.xla)|*.xla|" +
> "All files (*.*)|*.*" ;
> private string testFileName = "HostExcelWindo w.xls";
>
> 14 In the form's constructor, after the call to InitializeCompo nent, work
> out the path to the sample test workbook, and setup the OpenFileDialog
> properties:
>
> string currentLocation = System.Environm ent.CurrentDire ctory;
> string currentPath = currentLocation .Substring(
> 0, currentLocation .IndexOf(@"bin\ Debug"));
>
> openFile.Initia lDirectory = currentPath;
> openFile.Filter = ExcelFilter;
> openFile.Restor eDirectory = true;
> openFile.Title = "Open Excel File";
> openFile.Filter Index = 0;
> openFile.FileNa me = Path.Combine(cu rrentPath, testFileName);
>
> 15 In the button Click handler for the "Open Book" button, show the
> OpenFileDialog, to allow the user to choose a workbook to open. Then open
> the workbook using the control's exposed OpenWorkbook method:
>
> if (openFile.ShowD ialog() == DialogResult.OK )
> {
> try
> {
> excelControl1.O penWorkbook(ope nFile.FileName) ;
> cmdOpenBook.Ena bled = false;
> cmdCloseBook.En abled = true;
> cmdQuitExcel.En abled = true;
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
> }
>
> 16 In the Click handler for the "Close Book" button, invoke the control's
> CloseWorkbook method:
>
> try
> {
> excelControl1.C loseWorkbook();
> cmdCloseBook.En abled = false;
> cmdOpenBook.Ena bled = true;
> }
> catch (Exception ex)
> {
> MessageBox.Show (ex.Message);
> }
>
> 17 Add a new method to the form to invoke the control's QuitExcel method.
> (We'll call this in two places):
>
> private void QuitExcel()
> {
> excelControl1.Q uitExcel();
> cmdQuitExcel.En abled = false;
> cmdOpenBook.Ena bled = true;
> cmdCloseBook.En abled = false;
> }
>
> 18 The first place to call this method is the Click event handler for the
> "Quit Excel" button; and the second place is in the Closing event handler
> for the form itself (add this event handler using the events list in the
> properties pane).
>
> 19 Build and test:
>
> 20 Note: there are several race conditions in this system. To make the
> Excel control robust, we should hook events such as WorkbookBeforeC lose,
> WorkBookOpen, etc.
>
>
> If you have any concern on this issue, please post here.
>
>
> Best regards,
>
> Peter Huang
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
> This posting is provided "AS IS" with no warranties, and confers no rights.
>[/color]

[Peter Huang]

Re: C# Aplpication talking to Excel Add-In

Hi Matthew,

Thanks for posting in the community.

First of all, I would like to confirm my understanding of your issue.
From your description, I understand that it seems you will create a
remoteobject (ClassLibrary) which host a winform, also you will host the
remote object in the Excel Addin and publish the remoteobject.
In another application(As the remoting client), you will call the method of
the remote obejct to show a form which will handle the
DocEvents_Selec tionChangeEvent Handler event.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

Can you tell me why you wants to design your application in such way?
As the form will also create in the process of Excel Addin(Excel process),
why not directly show the form by click one button on the Addin.

Please Apply My Suggestion Above And Let Me Know If It Helps Resolve Your
Problem.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.