Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Excel add in development in C++ Aplications in finance - Dalton S.

Dalton S. Excel add in development in C++ Aplications in finance - Wiley publishing , 2005. - 425 p.
ISBN 0-470-02469-0
Download (direct link): exceladdindevelopmentincand2005.pdf
Previous << 1 .. 116 117 118 119 120 121 < 122 > 123 124 125 126 127 128 .. 168 >> Next

7 Note that xlAutoFree is an exception: it is a macro-sheet function equivalent, not a command.
Miscellaneous Topics 297
not covered here in order to keep this section simple and compiler-independent. However, most of the inefficiency can be removed with the use of static or global variables so that the interrogations need only be done once.
If you want to access COM-exposed Excel methods or properties other than those discussed in the following sections, you can fairly easily get the syntax and names of these from VBA, either by recording a macro or via the VBA Excel help.
As a final note before moving on, this section only shows code examples that work when part of a C++ source module. The syntax for C modules is a little different, and is not described, in the interests of simplicity.
9.5.1 Initialising and un-initialising COM
A number of things need to be initialised when the XLL is activated and then un-initialised when the XLL is deactivated. The following outline and code examples get around many of the inefficiencies of late binding by caching object references and dispatch function IDs (DISPIDs) in global or static variables.
The steps to initialise the interface are:
1. Include the system header <comdef.h> in source files using the COM/OLE interface.
2. Make sure Excel has registered itself in the ROT (Running Object Table).8
3. Initialise the COM interface with a call to OleInitialize(NULL).
4. Initialise a CLSID variable with a call to CLSIDFromProgID().
5. Initialise an IUnknown object pointer with a call to GetActiveObject(). If there are two instances of Excel running, GetActiveObject() will return the first.
6. Initialise a global pointer to an IDispatch object for Excel with a call to the QueryInterface() method of the IUnknown object.
The Excel.Application’s methods and properties are now available. The most sensible place to call the function that executes these steps is from xlAutoOpen(). The following code shows how these steps can be accomplished:
IDispatch *pExcelDisp = NULL; // Global pointer
bool InitExcelOLE(void)
{
if(pExcelDisp)
return true; // already initialised
// Make sure Excel is registered in the Running Object Table. Even // if it already has, telling it to do so again will do no harm. HWND hWnd;
if((hWnd = FindWindow("XLMAIN", 0)) != NULL)
{
// Sending WM_USER + 18 tells Excel to register itself in the ROT
SendMessage(hWnd, WM_USER + 18, 0, 0);
}
// Initialise the COM library for this compartment
8 The Microsoft Knowledge Base Articles 147573, 153025 and 138723 provide more background on this topic as well as links to related articles.
298
Excel Add-in Development in C/C++
OleInitialize(NULL);
CLSID clsid;
HRESULT hr; char cErr[64];
IUnknown *pUnk;
hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if(FAILED(hr))
{
// This is unlikely unless you have forgotten to call OleInitialize sprintf(cErr, "Error, hr = 0x%08lx", hr);
MessageBox(NULL, cErr, "CLSIDFromProgID",
MB_OK | MB_SETFOREGROUND); return false;
}
hr = GetActiveObject(clsid, NULL, &pUnk);
if(FAILED(hr))
{
// Excel may not have registered itself in the ROT sprintf(cErr, "Error, hr = 0x%08lx", hr);
MessageBox(NULL, cErr, "GetActiveObject",
MB_OK | MB_SETFOREGROUND); return false;
}
hr = pUnk->QueryInterface(IID_IDispatch,(void**)&pExcelDisp);
if(FAILED(hr))
{
sprintf(cErr, "Error, hr = 0x%08lx", hr);
MessageBox(NULL, cErr, "QueryInterface",
MB_OK | MB_SETFOREGROUND); return false;
}
// We no longer need pUnk pUnk->Release();
// We have now done everything necessary to be able to access all of // the methods and properties of the Excel.Application interface. return true;
}
When the XLL is unloaded the XLL should undo the above steps in the following order:
1. Release the global IDispatch object pointer with a call to its Release() method.
2. Set the global IDispatch object pointer to NULL to ensure that subsequent reactivation of the XLL is not fooled into thinking that the object still exists.
3. Un-initialise the COM interface with a call to OleUninitialize().
The most sensible place to call the function that executes these steps is xlAutoClose(), making sure that this is after any other function calls that might still want to access COM.
Miscellaneous Topics 299
The following code shows how these steps can be accomplished:
void UninitExcelOLE(void)
{
// Release the IDispatch pointer. This will decrement its RefCount pExcelDisp->Release(); pExcelDisp = NULL; // Good practice OleUninitialize();
}
Once this is done, the Excel application’s methods and properties can fairly straightforwardly be accessed as demonstrated in the following sections. Note that access to Excel’s worksheet functions, for example, requires the getting of the worksheet functions interface, something that is beyond the scope of this book.
9.5.2 Getting Excel to recalculate worksheets using COM
This is achieved using the Calculate method exposed by Excel via the COM interface. Once the above initialisation of the pExcelDisp IDispatch object has taken place, the following code will have the equivalent effect of the user pressing the {F9} key. Note that the call to the GetIDsOfNames() method is executed only once for the Calculate command, greatly speeding up subsequent calls.
Previous << 1 .. 116 117 118 119 120 121 < 122 > 123 124 125 126 127 128 .. 168 >> Next