By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,209 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,209 IT Pros & Developers. It's quick & easy.

C# Aplpication talking to Excel Add-In

P: n/a
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!

Nov 15 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
SetForegroundWindow 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("user32.dll")]
private static extern int SetForegroundWindow(int hwnd);
Excel.Application oXL=null;
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Workbooks oWBS=null;
Excel.Workbook oWB=null;
oXL = new Excel.ApplicationClass();
oWBS = oXL.Workbooks;
oWB = oWBS.Add(Missing.Value);
oWB.SheetSelectionChange +=new
Microsoft.Office.Interop.Excel.WorkbookEvents_Shee tSelectionChangeEventHandl
er(oWB_SheetSelectionChange);

this.textBox1.Text=oXL.ActiveCell.get_Address(true ,true,Excel.XlReferenceSty
le.xlA1 ,false,Missing.Value);
oXL.Visible=true;
}
private void oWB_SheetSelectionChange(object Sh,
Microsoft.Office.Interop.Excel.Range Target)
{

this.textBox1.Text=Target.get_Address(true,true,Ex cel.XlReferenceStyle.xlA1
,false,Missing.Value);
}

private void button2_Click(object sender, System.EventArgs e)
{
int rt = SetForegroundWindow(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.

Nov 15 '05 #2

P: n/a
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 "SetForgroundWindow" 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:
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
SetForegroundWindow 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("user32.dll")]
private static extern int SetForegroundWindow(int hwnd);
Excel.Application oXL=null;
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Workbooks oWBS=null;
Excel.Workbook oWB=null;
oXL = new Excel.ApplicationClass();
oWBS = oXL.Workbooks;
oWB = oWBS.Add(Missing.Value);
oWB.SheetSelectionChange +=new
Microsoft.Office.Interop.Excel.WorkbookEvents_Shee tSelectionChangeEventHandl
er(oWB_SheetSelectionChange);

this.textBox1.Text=oXL.ActiveCell.get_Address(true ,true,Excel.XlReferenceSty
le.xlA1 ,false,Missing.Value);
oXL.Visible=true;
}
private void oWB_SheetSelectionChange(object Sh,
Microsoft.Office.Interop.Excel.Range Target)
{

this.textBox1.Text=Target.get_Address(true,true,Ex cel.XlReferenceStyle.xlA1
,false,Missing.Value);
}

private void button2_Click(object sender, System.EventArgs e)
{
int rt = SetForegroundWindow(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.


Nov 15 '05 #3

P: n/a
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("user32.dll")]
public static extern int FindWindowEx(
int hwndParent, int hwndChildAfter, string lpszClass,
int missing);

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

[DllImport("user32.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(string 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.ApplicationClass 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 InitializeComponent,
assign initial values to the 3 simple integers from the system metrics:

captionHeight = SystemInformation.CaptionHeight;
borderWidth = SystemInformation.BorderSize.Width;
borderHeight = SystemInformation.BorderSize.Height;

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

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

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(xlWindow, this.Handle.ToInt32());

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.Open(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(xlWindow,
-borderWidth,
-(captionHeight +borderHeight),
this.Bounds.Width +borderWidth *2,
this.Bounds.Height +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.Focus();
}
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(missing, missing, missing);
Marshal.ReleaseComObject(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.Threading.Thread.Sleep(100);
}

Finally, cleanup our object reference and memory:

Marshal.ReleaseComObject(xl);
xl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
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.dll 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 = "HostExcelWindow.xls";

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

string currentLocation = System.Environment.CurrentDirectory;
string currentPath = currentLocation.Substring(
0, currentLocation.IndexOf(@"bin\Debug"));

openFile.InitialDirectory = currentPath;
openFile.Filter = ExcelFilter;
openFile.RestoreDirectory = true;
openFile.Title = "Open Excel File";
openFile.FilterIndex = 0;
openFile.FileName = Path.Combine(currentPath, 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.ShowDialog() == DialogResult.OK)
{
try
{
excelControl1.OpenWorkbook(openFile.FileName);
cmdOpenBook.Enabled = false;
cmdCloseBook.Enabled = true;
cmdQuitExcel.Enabled = 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.CloseWorkbook();
cmdCloseBook.Enabled = false;
cmdOpenBook.Enabled = 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.QuitExcel();
cmdQuitExcel.Enabled = false;
cmdOpenBook.Enabled = true;
cmdCloseBook.Enabled = 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 WorkbookBeforeClose,
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.

Nov 15 '05 #4

P: n/a
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 RemotingServices.Marshal. The application then gets a proxy to this
object via RemotingServices.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_SelectionChangeEventHandler 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:
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("user32.dll")]
public static extern int FindWindowEx(
int hwndParent, int hwndChildAfter, string lpszClass,
int missing);

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

[DllImport("user32.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(string 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.ApplicationClass 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 InitializeComponent,
assign initial values to the 3 simple integers from the system metrics:

captionHeight = SystemInformation.CaptionHeight;
borderWidth = SystemInformation.BorderSize.Width;
borderHeight = SystemInformation.BorderSize.Height;

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

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

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(xlWindow, this.Handle.ToInt32());

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.Open(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(xlWindow,
-borderWidth,
-(captionHeight +borderHeight),
this.Bounds.Width +borderWidth *2,
this.Bounds.Height +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.Focus();
}
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(missing, missing, missing);
Marshal.ReleaseComObject(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.Threading.Thread.Sleep(100);
}

Finally, cleanup our object reference and memory:

Marshal.ReleaseComObject(xl);
xl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
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.dll 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 = "HostExcelWindow.xls";

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

string currentLocation = System.Environment.CurrentDirectory;
string currentPath = currentLocation.Substring(
0, currentLocation.IndexOf(@"bin\Debug"));

openFile.InitialDirectory = currentPath;
openFile.Filter = ExcelFilter;
openFile.RestoreDirectory = true;
openFile.Title = "Open Excel File";
openFile.FilterIndex = 0;
openFile.FileName = Path.Combine(currentPath, 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.ShowDialog() == DialogResult.OK)
{
try
{
excelControl1.OpenWorkbook(openFile.FileName);
cmdOpenBook.Enabled = false;
cmdCloseBook.Enabled = true;
cmdQuitExcel.Enabled = 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.CloseWorkbook();
cmdCloseBook.Enabled = false;
cmdOpenBook.Enabled = 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.QuitExcel();
cmdQuitExcel.Enabled = false;
cmdOpenBook.Enabled = true;
cmdCloseBook.Enabled = 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 WorkbookBeforeClose,
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.


Nov 15 '05 #5

P: n/a
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:
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("user32.dll")]
public static extern int FindWindowEx(
int hwndParent, int hwndChildAfter, string lpszClass,
int missing);

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

[DllImport("user32.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(string 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.ApplicationClass 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 InitializeComponent,
assign initial values to the 3 simple integers from the system metrics:

captionHeight = SystemInformation.CaptionHeight;
borderWidth = SystemInformation.BorderSize.Width;
borderHeight = SystemInformation.BorderSize.Height;

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

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

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(xlWindow, this.Handle.ToInt32());

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.Open(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(xlWindow,
-borderWidth,
-(captionHeight +borderHeight),
this.Bounds.Width +borderWidth *2,
this.Bounds.Height +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.Focus();
}
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(missing, missing, missing);
Marshal.ReleaseComObject(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.Threading.Thread.Sleep(100);
}

Finally, cleanup our object reference and memory:

Marshal.ReleaseComObject(xl);
xl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
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.dll 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 = "HostExcelWindow.xls";

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

string currentLocation = System.Environment.CurrentDirectory;
string currentPath = currentLocation.Substring(
0, currentLocation.IndexOf(@"bin\Debug"));

openFile.InitialDirectory = currentPath;
openFile.Filter = ExcelFilter;
openFile.RestoreDirectory = true;
openFile.Title = "Open Excel File";
openFile.FilterIndex = 0;
openFile.FileName = Path.Combine(currentPath, 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.ShowDialog() == DialogResult.OK)
{
try
{
excelControl1.OpenWorkbook(openFile.FileName);
cmdOpenBook.Enabled = false;
cmdCloseBook.Enabled = true;
cmdQuitExcel.Enabled = 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.CloseWorkbook();
cmdCloseBook.Enabled = false;
cmdOpenBook.Enabled = 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.QuitExcel();
cmdQuitExcel.Enabled = false;
cmdOpenBook.Enabled = true;
cmdCloseBook.Enabled = 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 WorkbookBeforeClose,
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.


Nov 15 '05 #6

P: n/a
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_SelectionChangeEventHandler 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.

Nov 15 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.