Doing Excel Automation witch MSVC

Now fasten your seatbelt and be prepared for a real fast flight across
MSVC, Excel and Automation. I don’t really want to teach you understanding
automation, rather I like to have your job done. 🙂

Please bear in mind, that I will describe one special task here: storing
some information as an Excel sheet with auto-sized coloumns. I am using
MSVC 5.0 and the MFC.

I already have a prepared text file containing the information I would
like to store. I did this using semikolon (;) separated ascii-output with
new-linecarriage-return pairs at the end of each line. (To say it in plain
MFC, I used the CStdioFile class with the WriteString method)

If you are working with the Excel classes, you need to deal with the
VARIANT data type or, more convient when using MFC, with the COleDispatch
class, which has a built-in interface and is more easy to use.

A caveat of this approach that you have to modify the automation classes
by hand, but this caveat is supersed by the ability of removing unneeded
(and sometime ununderstood) parameters from the method calls.

Before going through the step-by-step procedure, something tells me
that you probably like to download my project
file
(96K).

Step zero: Record your task as Excel macro


First of all, you will have to be used to Excel’s VBA macro language. It
is VERY helpful if you first do the task you’d like to do in Excel while
recording this task as a macro.

The macro will look somewhat like this, hence, that VBA is nicely object
oriented:


Sub Macro1()

    Workbooks.OpenText Filename:=”C:Example.txt”,
Origin:= _


        xlWindows, StartRow:=1,
DataType:=xlDelimited, TextQualifier _


        :=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _


        Semicolon:=True, Comma:=False,
Space:=False, Other:=False, _


        FieldInfo:=Array(1,
1)


    Cells.Select

    Selection.Columns.AutoFit

    Workbook(1).SaveAs Filename:=”C:tempREADME.xls”,
FileFormat:= _


        xlNormal, Password:=””,
WriteResPassword:=””, _


        ReadOnlyRecommended:=False,
CreateBackup:=False


    ActiveWindow.Close

End Sub


 

Step one: Create the Excel automation classes


In your Excel program directory you will find the Type Library for the
Excel automation. I think, all of you will have the english version, Xl5en32.olb,
along with perhaps some localized version of this (e.g. the german version
is shipped with a Xl5de32.olb).

Using the Class Wizard, choose “New Class” and “from a type library”
(I have the german version, so I don’t know the exact english menu commands).
You are then asked to enter the path and filename of the type library.
Having done you get a list of classes. Looking at the Excel macro you see
which classes are needed. I needed five flasses (Application, Range, Workbook,
Workbooks, Worksheet). When in doubt you may choose all classes, but then
you have a bunch of classes that you don’t need.

 

Step two: Add methods for opening and closing Excel


In your class needing Excel create a member variable holding the Excel’s
application object


class CMyDemoDlg : public
CDialog


{

 // the ususal things

 …

private:

 Application m_Excel_en;

 …

 // other usual things

}

and the two functions OpenExcel() and CloseExcel() as follows:


bool CMyDemoDlg::OpenExcel()

{

    if (!m_bExcelStarted)
// this is a member-variable of type bool


       
if
(m_Excel.CreateDispatch(“Excel.Application”))


           
{


//

// un-comment these lines, is you want to
see what Excel is doing


//

//             
CWnd *pExelWnd = CWnd::FindWindow(“XLMAIN”, NULL);


//             
if (pExelWnd )


//             
{


//                 
pExelWnd->ShowWindow(SW_SHOWNORMAL);


//                 
pExelWnd->UpdateWindow();


//                 
pExelWnd->BringWindowToTop();


//             
}


//             
BringWindowToTop();


               
m_bExcelStarted=true;


           
}


    return m_bExcelStarted;

}

bool CMyDemoDlg::CloseExcel()

{

    if (m_bExcelStarted)

    {

        m_Excel_en.Quit();

        m_Excel_en.ReleaseDispatch();

        m_bExcelStarted=false;

    }

    return m_bExcelStarted;

}

In you application class add to the following lines to InitInstance():



BOOL CMyDemoApp::InitInstance()

{

    // initialize OLE libraries

    if (!AfxOleInit())

    {

        AfxMessageBox(IDP_OLE_INIT_FAILED);

        return
FALSE;


    }

    // do all the other things

    …

    return TRUE;

}

In your stdafx-Header you need to add the following line to include
the MFC automation support





#include <afxdisp.h>       
// MFC OLE-automation classes




 

Step three-and-four-combined: Start building the Excel macro as C++ Code while modifying the automation classes.


For every parameter you need, create a local variable of type COleVariant.
In Excel’s VBA you can omit parameter not needed. In the Excel automation
classes you can omit the parameters you do not need IF AND ONLY IF they
are at the end of the method’s parameters list. In the Excel-class create
a new overloaded member function with fewer parameters.

All automation functions, whose return value is used to create another automation
object should be modified to return a LPDISPATCH pointer rather than a VARIANT!

So, for example, you should take the method



VARIANT Application::Worksheets(const VARIANT&
Index)


{

    VARIANT result;

    static BYTE parms[]
= VTS_VARIANT;


    InvokeHelper(0x1ee, DISPATCH_METHOD, VT_VARIANT,
(void*)&result, parms, &Index);


    return result;

}

and create two new methods (the second with an omitted parameter)


LPDISPATCH Application::Workbooks(const VARIANT& Index)

{

    // function changed by
T.B.


    LPDISPATCH result;

    static BYTE parms[]
=  VTS_VARIANT;


    InvokeHelper(0x23c, DISPATCH_METHOD, VT_DISPATCH,
(void*)&result, parms,


        &Index);

    return result;

}

LPDISPATCH Application::Workbooks()

{

    // new by T.B.

    LPDISPATCH result;

    InvokeHelper(0x23c, DISPATCH_METHOD, VT_DISPATCH,
(void*)&result, NULL);


    return result;

}

To keep in mind, that you did something with the type library it is
good practice to enter a short comment saying “I did something”.

Do this anytime you are using a return value implicit or explicit.

My macro, ported to C++ now looks like this


void CMyDemoDlg::DoExcelConversion(CString
File)


{

    CString XlsFile =

           
Do.Some.Operations.To.Calculate.The.Excel.File.Name(File);


 

    // or say simply:

    // XlsFile=”C:\temp\demo.xls”;

    COleVariant FilenameOpen(File),

        FilenameSave(XlsFile),

        Origin((short)2),
// xlWindows


        StartRow((short)1),

        DataType((short)1),
// xlDelimited


        TextQualifier((short)1),
// xlDoubleQuote


        ConsecutiveDelimiter((long)FALSE,
VT_BOOL),


        Tab((long)FALSE,
VT_BOOL),


        Semicolon((long)TRUE,
VT_BOOL),


        Fileformat((short)33),
// xlExcel4


        Save((long)FALSE,
VT_BOOL);

    TRY

    {

       Workbooks Wbs(m_Excel_en.Workbooks());

      Wbs.OpenText(FilenameOpen, Origin,
StartRow, DataType,


           
TextQualifier, ConsecutiveDelimiter, Tab, Semicolon);

      Range ran(m_Excel_en.Columns(COleVariant(“A:Z”)));

 

      ran.AutoFit();

      Workbook Wb(m_Excel_en.Workbooks(COleVariant((short)1)));

      Wb.SaveAs(FilenameSave, Fileformat);

      Wb.Close(Save);

      // perhaps
you want to delete the text file,


      // because
now you have an Excel sheet


      // DeleteFile(File);

    }

    CATCH(COleDispatchException, e)

    {

         TRACE(e->m_strDescription);

         MessageBox(“Error
creating Excel-file:n”+e->m_strDescription,


           
“My Demo Dialog”, MB_OK);


    }

    END_CATCH;

}

 

Some people wondered how I know the numerical value of the Excel constants, like xlWindows,
xlExcel4 and so on. This is simple, but not trivial. Follow this step by step procedure:

A) Run Excel with an empty (or not) worksheet

B) Use Insert – Macro – Visual Basic Module

C) Type in a short dummy function. For example

Sub Dummy()
test = 1
End Sub

Make sure you have the proper language settings, this is important
only for users having a localized (non-english) version. For these
languages Excel allows you to use the VBA with language-dependend
keywords. If you are somewhat used in Basic with it’`s english keywords
then it feels HORRIBLE to use the (badly translated)
german-or-whatever-language-you-use keywords 🙂

[For our german-tongued friends:

For i=1 To 11 Step 2
Do.Some.Thing()
Next i

will become

F|r i=1 bis 11 Schrittweite 2
Tue.Irgend.Etwas()
Ndchste i

horrible…… 🙁
]

D) Put the cursor back to the second line (test=1) and start the single
step debugger(F8 key on my german system, or use Execute – single Step)

E) The debugger window pops up. Go to the “Watch” tab, make a right
mouse click into the upper half (the watch area) and choose “Add Watch”,
just like you do in DevStudio. As the watch expression just enter the
constant you need the value of, for example xlExcel4.

F) Ready. The watch window now tells you the value of xlWhateverYouLike

Some people wondered how I know the numerical value of the Excel constants. This is easy: just start excel with a short makro like this one:

Sub dummy()
    anotherDummy = 1
End Sub 

Then trace into this macro with the single step debugger, go to the watch window and add as watch variable the constant you need. If you enter xlWindows,
for example the watch window will tell you it has the value 2. There you are!

Step five: At the end, put all together


Now you are nearly done. Somehow retrieve the full path of the file you
wish to convert and do the following tree lines:


    OpenExcel();  // may
be called more than one time


    DoExcelConversion(FullPathName);

    CloseExcel(); // may be
called without a preceeding OpenExcel()


 

Now, at the end of my 200 lines page, I hope you are successful creating
your Excel classes and all the rest. If you someday have created a neatly
sold application using this knowledge and have made a billion dollar, remember
me and send me a check 🙂

IMPORTANT NOTE (due to a lot of emails concerning this): this demonstration code DOES NOT WORK with Excel 97. It was only tested and proved
working with Excel 5.0 and Excel 7.0 (some call it Excel 95) on both Windows NT 4.0 and Windows 95. I do not have Excel 97, if anyone is willing to
sponsor me a licence, so please, and I will try to do a good job….

If you find any errors or typos, please feel free to drop a mail.

Posted: 15 May 1998

More by Author

Must Read