try
{
_Application app; // app is an _Application object.
_Workbook book; // More object declarations.
_Worksheet sheet;
Workbooks books;
Worksheets sheets;
Range range; // Used for Microsoft Excel 97 components.
LPDISPATCH lpDisp; // Often reused variable.
// Common OLE variants. Easy variants to use for calling arguments.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
// Start Microsoft Excel, get _Application object,
// and attach to app object.
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't CreateDispatch() for Excel");
return;
}
// Set visible.
app.SetVisible(TRUE);
// Register the Analysis ToolPak.
CString sAppPath;
sAppPath.Format ("%s\\Analysis\\Analys32.xll", app.GetLibraryPath());
if(!app.RegisterXLL(sAppPath))
AfxMessageBox("Didn't register the Analys32.xll");
// Get the Workbooks collection.
lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
ASSERT(lpDisp);
books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
// to the books object.
// Open a new workbook and attach that IDispatch pointer to the
// Workbook object.
lpDisp = books.Add( covOptional );
ASSERT(lpDisp);
book.AttachDispatch( lpDisp );
// To open an existing workbook, you need to provide all
// arguments for the Open member function. In the case of
// Excel 2002 you need to provide 16 arguments.
// The code below opens a workbook and adds it to the Workbook's
// Collection object. It shows 13 arguments, required for Excel
// 2000.
// You need to modify the path and file name for your own
// workbook.
//
// lpDisp = books.Open("C:\\Test", // Test.xls is a workbook.
// covOptional, covOptional, covOptional, covOptional, covOptional,
// covOptional, covOptional, covOptional, covOptional, covOptional,
// covOptional, covOptional ); // Return Workbook's IDispatch
// pointer.
// Get the Sheets collection and attach the IDispatch pointer to your
// sheets object.
lpDisp = book.GetSheets();
ASSERT(lpDisp);
sheets.AttachDispatch(lpDisp);
// Get sheet #1 and attach the IDispatch pointer to your sheet
// object.
lpDisp = sheets.GetItem( COleVariant((short)(1)) );
//GetItem(const VARIANT &index)
ASSERT(lpDisp);
sheet.AttachDispatch(lpDisp);
// Fill range A1 with "1/25/98", the settlement date.
lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("1/25/98")); // Excel 97 & Excel 2000
range.SetValue2(COleVariant("1/25/98")); // Excel 2002
// Fill range A2 with "11/15/99", the maturity date.
lpDisp = sheet.GetRange(COleVariant("A2"), COleVariant("A2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("11/15/99")); // Excel 97 & Excel 2000
range.SetValue2(COleVariant("11/15/99")); // Excel 2002
// Fill range A3 with "2", the frequency for semi-annual interest
// payments.
lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("A3"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("2")); // Excel 97 & Excel 2000
range.SetValue2(COleVariant("2")); // Excel 2002
// Fill range A4 with 1, the basis (actual/actual).
lpDisp = sheet.GetRange(COleVariant("A4"), COleVariant("A4"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("1")); // Excel 97 & Excel 2000
range.SetValue2(COleVariant("1")); // Excel 2002
// Fill range C1 with the formula "=COUPNCD(A1, A2, A3, A4)" and
// format the cell with a Date type of the Number format.
lpDisp = sheet.GetRange(COleVariant("C1"), COleVariant("C1"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetNumberFormat(COleVariant("mm/dd/yy"));
range.SetFormula(COleVariant("=COUPNCD(A1, A2, A3, A4)"));
/* This is an alternative that works without placing variables on
// the worksheet.
// The values are arguments contained in the SetFormula() call.
// range.SetFormula(COleVariant(
"=COUPNCD(\"09/15/96\",\"11/15/99\",2,1)"));
*/
// *** The example in this block uses a built-in Microsoft Excel
// function.
// You do not have to register any add-in to use the built-in
// Microsoft Excel worksheet functions.
lpDisp = sheet.GetRange(COleVariant("C3"), COleVariant("C3"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetFormula(COleVariant("=SUM(A3, A4)"));
// or use:
// range.SetFormula(COleVariant("=SUM(2,1)"));
// *** End of example for built-in function usage.
// Release dispatch pointers.
range.ReleaseDispatch();
sheet.ReleaseDispatch();
// This is not really necessary because
// the default second parameter of AttachDispatch releases
// when the current scope is lost.
} // End of processing.
catch(COleException *e)
{
char buf[1024]; // For the Try...Catch error message.
sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
}
catch(COleDispatchException *e)
{
char buf[1024]; // For the Try...Catch error message.
sprintf(buf,
"COleDispatchException. SCODE: %08lx, Description: \"%s\".",
(long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));
::MessageBox(NULL, buf, "COleDispatchException",
MB_SETFOREGROUND | MB_OK);
}
catch(...)
{
::MessageBox(NULL, "General Exception caught.", "Catch-All",
MB_SETFOREGROUND | MB_OK);
}
