Excel automation from C++ with MFC

[Bob Ray]

After digging through the newsgroups for hours, decoding a ton of VB
code for automating Excel, converting it to C++ using VC 6.0 and MFC,
and enough hair pulling to make me bald, I thought I'd post this to
save others the trouble. I created the following include file
containing the constants used in most common arguments to the Excel
functions for searching, copying, sorting, etc. and a sample mfc
program segment that demonstrates their use.

Merry Christmas,

Bob

/* File: xlConstants.h

*******************************************
/ Constants used in Excel automation from
/ Visual C++
/
/ Courtesy of Bob Ray -- 2003
/
/ Note: The values of additional xl constants can be
/ found by running Excel and selecting
/ Tools | Macro | Visual Basic Editor.
/
/ Press F2 to get to the Object Browser.
/ Type the name of the constant in the search window.
/ Find the constant and select it. Look at the lower
/ left of the window to get the value and add
/ it to this list of defines.

/ You will also need to #include "excel9.h" which is
/ created on command by Visual Studio.
/ Note: If you are using "excel8.h" or earlier
/ some of the Excel functions take a different
/ number of arguments

********************************************/


// optional argument for default or ignored parameters
// (Excel uses the value used the last time the function
/ was called).

#define vOpt COleVariant((long) DISP_E_PARAMNOTFOUND, VT_ERROR)


// *********** Search Constants for range.find() ***********

// Example:
/* range.find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection, MatchCase, MatchByte)*/

// What takes a COleVariant
// e.g. COleVariant("FindThisString") or COleVariant(2314)


// After
// use vOpt




//LookIn

#define xlComments COleVariant((long) -4144)
#define xlFormulas COleVariant((long) -4123) // will find value in
any cell
#define xlValues COleVariant((long) -4163) // ignores hidden cells

//LookAt

#define xlWhole COleVariant((long) 1) // whole word search
#define xlPart COleVariant((long) 2) // partial word search

//SearchOrder (vOpt works here)

#define xlByRows COleVariant((long) 1)
#define xlByColumns COleVariant((long) 2)

//SearchDirection (required but usually has no effect)

#define xlNext (long) 1
#define xlPrev (long) 2

// MatchCase

#define xlMatchCase COleVariant((long) 1)
#define xlIgnoreCase COleVariant((long) 0)

// MatchByte

// ignored, use vOpt


// *********** Insert / delete constants ***********
// These tell Excel which way to shift cells after the action is
performed

//vOpt works here

#define xlRight COleVariant((long) -4161)
#define xlLeft COleVariant((long) -4159)
#define xlUp COleVariant((long) -4162)
#define xlDown COleVariant((long) -4121)
#define xlGuess COleVariant((long) 0)


// *********** Sort constants ***********

/* range.Sort(Key1, Key1Order, Key2, SortType, Key2Order, Key3,
Key3Order,Header,CustomOrder,MatchCase,Orientation,SortMethod); */

// For argument 1 (key1), use the following:

// VARIANT key1; // these lines set up first arg (key1) to sort
function
// V_VT(&key1) = VT_DISPATCH;
// V_DISPATCH(&key1) = objSheet.GetRange(COleVariant(buff),
COleVariant(buff));

// For key2 and key3, use the above or use vOpt if you don't need
them.


// Sort type (used when sorting pivot tables)

#define xlSortValues (long) 1
#define xlSortLabels (long) 2

// Sort order (Note: required but ignored for key2 and key3 if they
are vOpt)

#define xlAscending (long) 1
#define xlDescending (long) 2

// Header (Tells sort to leave first row of range alone when sorting)

#define xlHeader (long) 1
#define xlNoHeader (long) 2
// xlGuess defined above (long) 0

// CustomOrder

// Use vOpt

// Match Case
// Use MatchCase and IgnoreCase defined above,
// although they appear to have no effect here

// Sort Orientation
// (note: don't use xlByRows or xlByColumns here -- they're backwards)

#define xlTopToBottom (long) 1
#define xlLeftToRight (long) 2

//Sort Method

#define xlPinYin (long) 1 // this is the default
#define xlStroke (long) 2

// End of include file excel9.h


***************

Here's some MFC C++ code that opens Excel gets the number of rows and
columns, searches for a name, inserts a row, sets its value and sorts
the entire spreadsheet.

***************

void CAutoProjectDlg::OnRun()
{


// operates on spreadsheet c:\test.xls, a gradebook.
// code Searches for a name,
// adds a student,
// and then sorts the sheet.

// sheet has the following format:

// A B C D E F
// 1 IdNum Name Test1 Test2 Total Grade
// 2 143564 Adams
// 3 324534 Jones
// 4 323456 Wilson

// note: Total and Grade columns contain formulas


_Application app;
_Workbook objBook;
Workbooks objBooks;
Worksheets objSheets;
_Worksheet objSheet;
Range range;


// Start Excel and get Application object...

if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't start Excel.");
return;
}

if(!UpdateData(TRUE)) {
AfxMessageBox("Can't Update");
return;
}


objBooks = app.GetWorkbooks();

CString name(_T("C:\\Test.xls"));

objBook = objBooks.Open(name,
vOpt, vOpt, vOpt, vOpt, vOpt, vOpt,
vOpt, vOpt, vOpt, vOpt, vOpt, vOpt);

objSheets = objBook.GetWorksheets();

// get sheet 1 of workbook
objSheet = objSheets.GetItem(COleVariant((short)1));

objSheet.Activate();



// This section gets the number of rows and columns
// in case we're curious

Range objTotal, objCols, objRows;


objTotal.AttachDispatch(objSheet.GetUsedRange());
objCols.AttachDispatch(objTotal.GetColumns());
objRows.AttachDispatch(objTotal.GetRows());

int NumRows = objRows.GetCount();
int NumCols = objCols.GetCount();

char buff[44];

//sprintf(buff,"Cols: %d",NumCols);
//AfxMessageBox(buff);

//sprintf(buff,"Rows: %d",NumRows);
//AfxMessageBox(buff);


// This section searches the second column for the string "TestString"


range = objSheet.GetRange(COleVariant("B2"),COleVariant("B60"));



if ( range.Find(COleVariant("Jones"), vOpt, xlFormulas, xlPart,
xlByRows, xlNext,xlIgnoreCase,vOpt) ){
AfxMessageBox("Found");
} else {
AfxMessageBox("Not Found");
}

// Now we'll insert a blank row just below the header row.

range = objSheet.GetRange(COleVariant("A2"),COleVariant("Z2"));

range.Insert(vOpt);

// Now we'll copy data from row 65 into the inserted row so
// it will contain the formulas.


Range src, dest;

src = objSheet.GetRange(COleVariant("A65"),COleVariant("Z65"));
dest= objSheet.GetRange(COleVariant("A2"),COleVariant("Z2"));

dest.SetValue(src.GetValue());

// Now that the inserted row has all the proper formulas, we'll set
// the data in cells A2 and D2.
// Note: m_ID and m_Name are variables set elsewhere in a dialog box.

//Set value of A2

range = objSheet.GetRange(COleVariant("A2"),COleVariant("A2"));
CString s;

// m_ID is a number we're storing as a string so we put a single
// quote in front of it
s.Format("'%s",m_ID);
range.SetValue(COleVariant(s));

//Set value of B2 (m_Name is already a string)
range = objSheet.GetRange(COleVariant("B2"),COleVariant("B2"));
range.SetValue(COleVariant(m_Name));

// Now we sort the entire sheet (leaving the header row untouched)

sprintf(buff,"B1"); // set column B as the key to sort on

VARIANT key1; // these lines set up first arg (key1) to sort
function
V_VT(&key1) = VT_DISPATCH;
V_DISPATCH(&key1) = objSheet.GetRange(COleVariant(buff),
COleVariant(buff));


range = objSheet.GetRange(COleVariant("A1"),COleVariant("Z70"));
range.Sort(key1, xlAscending, vOpt, vOpt, xlAscending, vOpt,
xlAscending,xlHeader,vOpt,xlIgnoreCase,xlTopToBottom,xlPinYin);

// Let the user see our work and
// decide when to close the spreadsheet.

app.SetVisible(TRUE);
app.SetUserControl(TRUE);

}

// End of C++ code segment

Hope this helps someone,
Bob

 

[Bob Ray]

Oops. The following code from the example program in my previous post
*won't* copy the formulas correctly. It will convert them to their values.

See the replacement code below, which also does a better job of copying
cell formats.

// Now we'll copy data from row 65 into the inserted row so
// it will contain the formulas.


Range src, dest;

src = objSheet.GetRange(COleVariant("A65"),COleVariant("Z65"));
dest= objSheet.GetRange(COleVariant("A2"),COleVariant("Z2"));

dest.SetValue(src.GetValue());

Replace the above code with the following:

Range src, dest;

src = objSheet.GetRange(COleVariant("A65"),COleVariant("Z65"));
dest= objSheet.GetRange(COleVariant("A2"),COleVariant("Z2"));


VARIANT copyTo;
V_VT(&copyTo) = VT_DISPATCH;
V_DISPATCH(&copyTo) = dest;

src.Copy(copyTo);

*************************

Bob