Is there any way to subclass excel?
-
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
-
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
-
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
-
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
-
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
-
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") ren -
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") renThanks 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
-
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