Re: Is there any way to subclass excel? - C / C++ / MFC Discussion Boards

hi,

I want to trap a events whenever user selects cell in excel sheet 2007. Can I do it by subclassing it. If yes how to do it.

Thanks

SNI

You need a different mechanism than to subclass to catch Windows messages.

Instead, you need to tell the relevant worksheet that you want to connect to its SelectionChange[^] event.

This CodeProject article[^] has information on COM connection points (that's what you need to use to catch the event).

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

Hi,
Thanks for your reply. But will it be same for Exccel 2007.

Thanks

SNI

Yes[^]

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

Thanks for your reply. I was able to capture events like WorkbookOpen,WorkbookActivate,SheetSelectionChange but my requirement is something different. Let assume that one excel file is open (Test.xlsx) and I have selected a cell and press F2 so that my cursor is inside a cell. Now I have opened this xlsx file so I know the name of it. but if I change the focus from this xlsx file to test.doc and again come back to Test.xlsx, I am not able to get the name of this file since the cursor is blinking inside the cell.
Here I want to capture this event either Active cell, which is not working nor all of the above mentioned events. How to capture this curosr blinking event inside the cell (EditDirectlyInCell).
Does Subclassing of Excel will help?

Thanks

SNI

SNI wrote:
Does Subclassing of Excel will help?

I don't know. It's difficult to tell, because I don't know what you're trying to accomplish - why you feel you need such a fine granularity view of what Excel's doing.

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

Actually it is a requirement where I need to find out the file name and when I am selecting xlsx file again and cursor is in cell then we are not able to get any event. I trying to find out what event it fires when xlsx file gets a focus again with cursor is in cell.

SNI

I see now. I tried this little program to interrogate the running instance of Excel (2007 in my case as well):

#include <iostream>
#import "libid:00020813-0000-0000-C000-000000000046" version("1.6") auto_search no_dual_interfaces raw_dispinterfaces rename("DialogBox", "excelDialogBox") rename("RGB", "excelRGB") rename("DocumentProperties", "excelDocumentProperties") rename("SearchPath", "excelSearchPath") rename("CopyFile", "excelCopyFile") rename("ReplaceText", "excelReplaceText")


int _tmain(int argc, _TCHAR* argv[])
{
   CoInitializeEx(0, COINIT_MULTITHREADED);
   try
   {
      Excel::_ApplicationPtr xl;
      if (SUCCEEDED(xl.GetActiveObject(__uuidof(Excel::Application))))
      {
         if (Excel::_WorkbookPtr wb = xl->ActiveWorkbook)
         {
            std::cout << "Getting name\n";
            _bstr_t xlName = wb->FullName;
            std::cout << xlName << std::endl;
            if (Excel::SheetsPtr sheets = wb->Worksheets)
            {
               std::cout << sheets->Count << std::endl;
            }
         }
      }
   }
   catch(_com_error& e)
   {
      std::cout << "EXCEPTION!!!\n";
      std::cerr << CT2CA(e.ErrorMessage()) << std::endl;
   }
   CoUninitialize();
	return 0;
}

When you're NOT editing a cell, this program works fine, printing the active workbook's file name and number of worksheets.

When you ARE editing a cell, it throws an exception (indicating an error status) when you interrogate the workbook object. That exception's text is "The message filter indicated that the application is busy". Now, I think that indicates that Excel is in a modal message loop and will not respond to any COM calls. Which means that Excel's window procedure isn't being called, so sub-classing Excel's window class won't help you at all anyway.

What you can do is (if Excel is busy) catch the Excel application's events. I think you'll find that Excel's SheetChange and AfterCalculate events are raised when you finish editing a cell. At that point, Excel won't be busy, so you can get the workbook's filename.

The resultant code could look something like this:

#include <iostream>
#include <atlcom.h>
#import "libid:00020813-0000-0000-C000-000000000046" version("1.6") auto_search no_dual_interfaces raw_dispinterfaces rename("DialogBox", "excelDialogBox") rename("RGB", "excelRGB") rename("DocumentProperties", "excelDocumentProperties") rename("SearchPath", "excelSearchPath") rename("CopyFile", "excelCopyFile") rename("ReplaceText", "excelReplaceText")

_ATL_FUNC_INFO SheetChangeInfo = { CC_CDECL, VT_EMPTY, 2, { VT_DISPATCH, VT_DISPATCH } };

class ExcelAppEventHandler : public IDispEventSimpleImpl<1, ExcelAppEventHandler, &__uuidof(Excel::AppEvents)>
{
public:
   ExcelAppEventHandler(bool& doneFlag) : done_(doneFlag) { done_ = false; }
   BEGIN_SINK_MAP(ExcelAppEventHandler)
      SINK_ENTRY_INFO(1, __uuidof(Excel::AppEvents), 0x0000061c, &ExcelAppEventHandler::SheetChange, &SheetChangeInfo)
   END_SINK_MAP()

   void _stdcall SheetChange(IDispatch * Sh, struct Excel::Range * Target)
   {
      done_ = true;
   }
private:
   bool& done_;
};

_bstr_t GetActiveWorkbookName(Excel::_ApplicationPtr xl)
{
   if (Excel::_WorkbookPtr wb = xl->ActiveWorkbook)
   {
      try
      {
         return wb->FullName;
      }
      catch(_com_error& e)
      {
         std::cout << "EXCEPTION!!!\n";
         std::cerr << CT2CA(e.ErrorMessage()) << std::endl;

         bool done;
         ExcelAppEventHandler app(done);
         if (SUCCEEDED(app.DispEventAdvise(xl)))
         {
            MSG msg;
            while (!done && GetMessage(&msg, NULL, 0, 0) > 0) {
               TranslateMessage(&msg);
               DispatchMessage(&msg);
            }            
            app.DispEventUnadvise(xl);
            return GetActiveWorkbookName(xl);
         }
      }
   }
   return _bstr_t();
}

int _tmain(int argc, _TCHAR* argv[])
{
   CoInitializeEx(0, COINIT_APARTMENTTHREADED);
   {
      Excel::_ApplicationPtr xl;
      if (SUCCEEDED(xl.GetActiveObject(__uuidof(Excel::Application))))
      {
         std::cout << "Getting name\n";
         std::cout << GetActiveWorkbookName(xl) << std::endl;
      }
   }
   CoUninitialize();
	return 0;
}

The GetActiveWorkbookName function attempts to get the workbook name. If it fails, it tells Excel that it wants events, then when it gets SheetChange it returns, as Excel is no longer busy.

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

Thanks for your reply. I have tried above code but it is giving me following error
"fatal error C1083: Cannot open type library file: 'libid :00020813-0000-0000-C000-000000000046': No such file or directory" on VC++ 6.0. what needs to be done in order to compile this.

Thanks

SNI

VC 6.0...that doesn't support using a libid for #import. You'll have to find the Excel type library file yourself and import it.

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

HI
i have given a path of cmd.exe while debugging a console application in "Executable debug session " dialog box.
Please tell me how to remove it .
Thanks

In the project properties, the path for cmd.exe will be available in Debugging -> Command.

«_Superman_»
I love work. It gives me something to do between weekends.

Hello,

I have a window derived from CMDIChildWnd which when maximized doesnt merge with the main window.

class TableFrame : CMDIChildWnd
{

afx_msg void OnSize(UINT nType, int cx, int cy);

}

void TableFrame::OnSize(int nType, int cx, int cy)
{
switch(nType)
{
case SIZE_MAXIMIZED :
{
CRect ParentRect;
GetParent()->GetClientRect(ParentRect);
MoveWindow(ParentRect,true);
break;
}
}

Prithaa

prithaa wrote:
I have a window derived from CMDIChildWnd which when maximized doesnt merge with the main window.

That's nice.

Is that a problem or not? Difficult to say as you're not asking a question...

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

...and what is your question?

Codeproject MVP for C++, I can't believe it's for my lounge posts...

Thanks for your replies

My question is when the child window is maximized it doesn't merge with the main window.
How should I do it?

Prithaa

Why are you changing things in WM_SIZE? Just let windows do it's job, and it will do it by itself.

I think the reason that it's failing is that you're getting in the way! WM_SIZE is largely meant for information purposes, or changing scroll bar meanings, etc.

Create a brand new MDI project, don't add any code, and you'll see if I'm right or not.

Iain.

Codeproject MVP for C++, I can't believe it's for my lounge posts...

Thanks for answers

I ll do that and see.But I have done that earlier , what happened at that time was the child window merged with the main window but now the main window didn't get restore button , the main window has minimize,maximize and close buttons

Prithaa

If it's anything like any MDI program I've worked on, the main window keeps it's normal buttons, but gains a smaller set just below them for the MDIChild.

I feel pretty sure that the bare wizard generated MDI app will work that way. If yours does not, you'll have to tear away more of your software until you get back to basic behaviour, then build back up again.

Iain.

Codeproject MVP for C++, I can't believe it's for my lounge posts...

Thanks for your replies

I ll check up

I have a program that connects to a database in a local 2008 SQL server to create a couple of tables. On most machines the connection time is about a second but on mine it was taking several seconds. I've just discovered that if I change the connection protocol from shared memory to TCP/IP, the connection time drops back to around a second. The same problem occurs if I boot either Vista64 or Windows 7 beta (two separate SQL server installations). The main difference between my machine (HP xw4600) and others is the CPU, I have a quad core where as the others will be dual cores. Can anyone suggest what may be wrong?

I can imagine that it takes longer to create and open the file mapping for shared memory, than it takes to create a new socket and connect to the server socket.

But that's only a one time cost. Why not do some testing to see if the performance for using the database is faster or slower for one type of connection over the other?

There's no problem with speed once the connection has been established. I've also discovered that I only had to enable TCP/IP to speed up the connection. Previously I was disabling shared memory as well. The order of preference has shared memory before TCP/IP so I've no idea what's going on!

I am making a project depending on mfc. in this project ,i created a object(here named it xobj) ，which is defined by myself， in the cmainwnd object construction, and destructed it in the cmainwnd object destruction. but in one cmainwnd object member function,i created a thread and also pass the pointer of the xobj into the thread. When the application is closed, destruction of cmainwnd object is called so as the xobj destruction,but at some time the thread is not end ,it still has the pointer of the xobj which means nothing now.
How to solve this?
I have tried the event,I made the del xobj wait until the thread is end in the destruction of cmainwnd object.but it seems not work.why?

tyr2000 wrote:
I have tried the event,I made the del xobj wait until the thread is end in the destruction of cmainwnd object.but i seems not work.why?

Post some relevant code of your destructor.

Cédric Moonen
Software developer

Charting control [v1.5]
OpenGL game tutorial in C++