I am trying to create a stand alone application in Visual Studio 2008 C# .Net that will house Excel Workbooks (2007). I am using Office.Interop in order to create the Excel application and I open the workbooks via Workbooks.Open(...).
The Interop does not provide any functionality to "move" the workbooks onto a form so I turned to P/Invoke Win32 library. I am able to move the entire excel application onto a WinForm with great success:
// pseudo code to give you the idea
excel = new Excel.ApplicationClass();
SetParent(excel.Hwnd, form.handle);
This allows me to customize the form and control user input. All right click commands and formula editing work properly.
Now, the issue I run into is when I want to open two workbooks in two separate forms. I do this by creating two excel application classes and placing each of those in their own form. When I try to reference one workbook to another workbook via =[Book2]Sheet1!A1, for example, it does not update. This is expected as each application is running under its own thread/process.
Here are the alternatives I have tried. If you have any suggestions I would be greatly appreciative.(OLE is not an option. VSTO must be available)
Create a single application class and move the workbook window into my form. Results: The window moves into my form and displays correctly, however, no right click or left click works on the form and it never gains focus. (I have tried to manually set focus and it does not work either). My guess is, by moving the window outside of the XLDESK application (viewable in Spy++ for Excel Application), the workbook application (EXCEL7) does not receive the correct window messages to gain focus and to behave properly. This leads me to:
Move the XLDESK window handle into my form. Results: This allows the workbook to be click-able again but also has an undesired result of moving all child windows into the same form.
Create a main excel application that creates workbooks. Create a new excel application for each new window. Move the workbook under the new excel application XLDESK window. Results: This also has the same effect of the 1st option. Unable to click in the workbook. This must mean that the thread that created the workbook is also responsible for the events.
Create a windows hook that watches the WndProc procedure. Results: No events watched. The targeted thread must export the hook proc in a DLL export call. Excel does not do this and thus you cannot inject into it's DLL (unless someone can prove me wrong). I am able to watch all threads within my own process but not from an outside process. Excel is created as a separate process.
Subclass NativeWindow. Results: Same as #4. After I move the window into my form, all events are captured up until the mouse is directly over the excel sheet making the sheet seem unclickable.
One idea I haven't tried yet is just to continually save the excel sheet as the user edits it. This should update all references but I would feel this would cause poor system performance. There will be numerous chart references as well and I'm not sure if this solution would cause problems further down the road.
I think in the end, all the workbooks need to be created by the same Excel Application and then moved to get the desired results but I can't seem to find the correct way to move the windows without disabling the user input in the process.
Any suggestions?
