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-line\carriage-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:\temp\README.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



Comments

  • uno de los estilos GHD están en descuento

    Posted by wanzilucky on 06/05/2013 08:50pm

    [url=http://comprar-ghd.manifo.com/]Comprar ghd[/url] Actualmente en el mercado vemos una gran variedad de planchas de pelo con características novedosas y con avanzada tecnología que son muy buenas para todos nosotros. Es recomendable utilizar una plancha que tenga placas de cerámica y turmalina, las planchas de titanio son buenas, con ellas puedes alisar el cabello sin problema.Lo que hace el titanio, es distribuir el calor de forma uniforme sobre todo el plato de la plancha. Ahora, gracias a la tecnología, tenemos planchas que combinan la cerámica con la turmalina, estas son las planchas mas recomendadas por los profesionales. [url=http://www.planchasespanaghdtop.net/]GHD Plancha España[/url] En un nuevo diseño con placas y detalles en rosa intenso que la convierten en una verdadera edición de culto, ghd styler PINK Cherry Blossom incorpora la misma tecnología que las stylers Gold Series y en esta ocasión se presenta en una funda térmica para la styler y una bolsa térmica para el secador, ideales para viajar o para prevenir golpes.GHD planchas alisadoras de cerámica se han desarrollado dentro de la de 2000.La ingeniería de la peluquería ofrece expuesta la cara particular con respecto a los estilistas y también iconos de tendencia en todo el mundo. [url=http://www.planchasespanaghdtop.net/]ghd españa tienda[/url] A principios de este mes te dije que el Cloud Nine Hair Styler (creado por los fundadores de la plancha de pelo ghd icónico) ha puesto en marcha en Australia con algunos miembros del ex Marineros de agua dulce a nivel local para lanzar la nueva plancha ghd en nuestro saturado mercado. Desde siempre he tenido curiosidad acerca de la competencia de los productos de belleza (ahora mismo mineral verdadero maquillaje es fascinante, yo también) que no podía decir que no cuando uno se me ofreció en préstamo.

    Reply
  • Give den bedste ghd glattejern, professionel personlig pleje i Danmark

    Posted by motherdhmm on 05/30/2013 05:53am

    [url=http://www.buy-beatsdrdre.com/category/new-beats-dr-dre-headphones]Beats By Dr Dre[/url] Mange tror at det er nemt nok at vælge et ghd glattejern. Altså når man først har besluttet sig for at man vil have et ghd glattejern og ikke et af de andre mærker inden for glattejern. Men der findes flere forskellige modeller inden for ghd glattejern. Hvilket ghd glattejern du skal vælge afhænger af hvad krav du stiller til det og hvad type hår du har. Har du langt krøllet hår og glatter det ofte, måske endda dagligt, så kan du med fordel vælge en af de brede ghd glattejern der findes. [url=http://www.buy-beatsdrdre.com/]beats by dre headphones[/url] Vi er bekymrede over deres foretrukne stjerner, ikke blot mere forståelse for, hvad de siger, vil de smager mere bekymret for den kjole, retro-mode i dag er stadig gal bunden, Ms krøllet hår end glat hår så populær og mere volumen hår design er ikke svært at se, hvad du vælger ghd fladjern og har evnerne, så dagens gav dig et hår pind. [url=http://www.buy-beatsdrdre.com/category/monster-beats-australia]monster beats australia[/url] Men det er vigtigt at notere sig at varmepladerne inden i dit ghd glattejern er samme størrelse som de keramiske plader udvendigt. Så jo større glattejern, jo større varmeplader. Dette har man gjort for at sikre sig at alle ghd glattejern har den helt rigtige og helt jævne fordeling af varmen. På den måde sikre ghd sig nemlig at deres glattejern glatter dit hår ordentligt første gang og glatter det hår du arbejder med helt lige og jævnt.

    Reply
  • FNwJU Zey gcBH

    Posted by eHSrqxUHoW on 04/17/2013 01:05am

    buy tramadol online tramadol ls 100 mg - tramadol withdrawal after 3 weeks

    Reply
  • worker

    Posted by Craig Lytle on 05/02/2012 06:31am

    can u get me a workinging sp7en32.olb mine doesn't work

    Reply
  • http://www.ucancode.net

    Posted by Legacy on 11/14/2003 12:00am

    Originally posted by: UCCDraw ActiveX Control is an ActiveX control that allows creation and editing of Visio-style charts from within your application. Allows you to create flow charts, vector drawings, raster images and more with the ability to include hyperlinks and various shading and coloring effects. You can group objects together, include images and text, link them together and apply custom drawing effects to create charts similar to Microsoft Visio, Adobe Illustrator, and CorelDRAW

    Great!

    Reply
  • how to avoid Oledlg prompt when use VC++6.0 to access Excel

    Posted by Legacy on 11/11/2003 12:00am

    Originally posted by: jason

    I use folling C++ code,but I puzzled with the Olebusy prompt, thanks for any help.
    
    

    #define xlWorkbookNormal -4143
    #define xlNochange 1

    clss CIOExcel
    {
    private:
    _Application m_ExcelApp;
    Workbooks m_ExcelWorkbooks;
    _Workbook m_ExcelWorkbook;
    Worksheets m_ExcelWorksheets;
    _Worksheet m_ExcelWorksheet;
    COleVariant m_ExcelOptional;

    private:
    bool InitExcelVariables();
    void DestroyExcelVariables();
    CIOExcel();
    ~CIOExcel();
    public:
    CSVToExcel(CString tsCsvFileName, CString tsExcelFileName,CString tsSheetName);
    };

    CIOExcel::CIOExcel()
    {
    CoInitialize(NULL);
    }

    CIOExcel::~CIOExcel()
    {
    CoUninitialize();
    }

    bool CIOExcel::InitExcelVariables()
    {
    try
    {
    ExcelWorkbooks = NULL;
    m_ExcelApp = NULL;
    m_ExcelWorkbook = NULL;
    m_ExcelWorksheet = NULL;
    CIOException pError;
    CString sError;
    m_ExcelOptional.vt = VT_ERROR;
    m_ExcelOptional = (long)DISP_E_PARAMNOFOUND;

    bool bRet = false;
    bRet = m_ExcelApp.CreateDispatch("Excel.Application", &pError);
    if (bRet == false)
    {
    AfxMessageBox("can not run Excel!");
    return false;
    }
    return true;
    }
    catch(...)
    {
    AfxMessageBox("can not run Excel!");
    return false;
    }
    }

    void CIOExcel::DestroyExcelVariables()
    {
    try
    {
    if (m_ExcelWorkbooks != NULL)
    {
    m_ExcelWorkbooks.Close();
    m_ExcelWorkbooks = NULL;
    }

    if (m_ExcelApp != NULL)
    {
    m_ExcelApp.Quit();
    m_ExcelApp = NULL;
    }
    }
    catch(...)
    {
    }
    }

    void CIOExcel::CSVToExcel(CString tsCsvFileName, CString tsExcelFileName,CString tsSheetName)
    {
    if (InitExcelVariables() != true)
    {
    DestroyExcelVariables();
    return false;
    }

    try
    {
    m_ExcelApp.SetDisplayAlerts(FALSE);
    m_ExcelWorkbooks = m_ExcelApp.GetWorkbooks();

    //open a csv file
    m_ExcelWorkbook = m_ExcelWorkbooks.Open((LPCTSTR)tsCsvFileName, m_ExcelOptional, m_ExcelOptional , m_ExcelOptional, m_ExcelOptional , m_ExcelOptional , m_ExcelOptional , m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional);
    COleVariant vFilename(tsExcelFileName);
    const short i = xlWorkbookNornal;
    COleVariant v1(i);

    m_ExcelWorkbooks.SetSaved(TRUE);
    m_ExcelWorkbook.SaveAs(vFilename, v1, m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional ,xlNoChange, m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional ,m_ExcelOptional);
    DestroyExcelVariables();
    return true;
    }
    catch(...)
    {
    DestroyExcelVariables();
    return false;
    }
    }

    Problem:
    When a csv file name by tsCsvFileName is small size, the codes run well, when the size begin large, for example, 3000 rows,
    50 columns, The Oledlg with type OleBusy shall display.
    The Dialog prompt: "because another Application running, the Operation can't complete. Please select 'Switch to' to activate the running Application", with Caption Title "Server Busy", and Buttons "Switch to ..", "Retry".

    How to avoid the boiling prompt?

    Reply
  • How to kill excel.exe from memory?

    Posted by Legacy on 09/23/2003 12:00am

    Originally posted by: Mottet

    Hello,
    
    I develop an application in vc++ and I use the OLE technology to drive excel.

    I've a problem when i quit my application. The excel.exe program stays always in memory. Also, we don't want any interaction with the end-user to confirm to close excel.

    To quit the application, i've developed this function :
    void CRFenPrincipale::finalizeOle()
    {
    if (this->getClasseurExcel() != NULL)
    {
    this->getClasseurExcel()->quit( true );
    this->getClasseurExcel()->supprimeFichier();
    delete this->getClasseurExcel();
    this->setClasseurExcel((CRClasseurDossier*)NULL);
    }
    if (this->getOleInitializationCode() == S_OK)
    OleUninitialize();
    }

    void PExcelAutomation::quit( const bool quiet )
    {
    if( _connected )
    {
    if (quiet)
    {
    _application.SetDisplayAlerts( false ) ;
    }

    _application.Quit();
    _application.ReleaseDispatch();
    }

    }

    Thank you very much for your help

    emmanuel


    PS :
    To initialize the excel automation, i've developed this function :
    const bool PExcelAutomation::init()
    {
    if (!_connected)
    {
    // R�cup�ration du CLSID d'Excel dans le registry
    CLSID clsid ;
    if (CLSIDFromProgID(OLESTR("Excel.Application.9"), &clsid) == NOERROR)
    {
    // Recherche du server Excel s'il est lanc�
    LPUNKNOWN lpUnk ;
    LPDISPATCH lpDispatch ;
    if (GetActiveObject(clsid, NULL, &lpUnk) == NOERROR)
    {
    HRESULT hr = lpUnk->QueryInterface(IID_IDispatch, (LPVOID*)&lpDispatch) ;
    lpUnk->Release() ;
    if (hr == NOERROR)
    {
    _application.AttachDispatch(lpDispatch, TRUE) ;
    }
    }

    // On v�rifie si le serveur a �t� lanc�
    bool createOk = _application.m_lpDispatch != NULL;

    // Si le serveur n'a pas encore �t� lanc�
    if (!createOk)
    {
    // On lance le serveur
    COleException ex;
    createOk = _application.CreateDispatch(clsid, &ex) != 0;
    }

    // Si le serveur est lanc�( il devrait l'�tre � ce stade)
    if (createOk)
    {
    // R�cup�ration de la listes des classeurs
    LPDISPATCH currentWorkbooks = _application.GetWorkbooks() ;
    _workbooks.AttachDispatch( currentWorkbooks ) ;

    _connected = _workbooks.m_lpDispatch ? true : false;

    // On initialise le VARIANT utilis� pour les param�tres optionnels non pass�s
    VariantInit(&_vNotPassed) ;
    V_VT(&_vNotPassed) = VT_ERROR ;
    V_ERROR(&_vNotPassed) = DISP_E_PARAMNOTFOUND ;
    }
    }
    }

    // On renvoie le succ�s de l'op�ration
    return _connected;
    }

    Reply
  • Excel 2000 automation with MFC

    Posted by Legacy on 06/30/2003 12:00am

    Originally posted by: Rajesh

    Hello,

    I am trying to automate the process of importing text data from a text file with delimiters as tabs.But when i follow the code suggested by thomas.I doubt at
    LPDISPATCH Application::Workbooks()
    {
    // new by T.B.
    LPDISPATCH result;
    InvokeHelper(0x23c, DISPATCH_METHOD, VT_DISPATCH, (void*)&result, NULL);
    return result;
    }

    I am getting an error member not found.I dont know where I have done wrong as this is the first time I am playing with excel.Can any body please suggest me what might be wrong.

    Thanks in advance,
    Rajesh Kanaparti

    • learning

      Posted by qiaoli on 09/23/2009 11:34pm

      I am just a beginning learner!

      Reply
    Reply
  • Excel 8 automation

    Posted by Legacy on 10/07/2002 12:00am

    Originally posted by: Thomas Blenkers

    I have just stumbled about this link providing at least some help to automate something newer than XL 97:

    http://ourworld.compuserve.com/homepages/John_Maddock/

    But still sorry, I can't help automating s.th. other than described here!

    Thomas

    Reply
  • Things have changed with newer versions of Excel

    Posted by Legacy on 04/05/2002 12:00am

    Originally posted by: Nathan Schultz

    Things have changed dramatically in Excel 2000 and Excel 2002. You don't need to do a lot of these things if you use the correct type libraries for the later versions. Here is a list of the type libraries:
    Office Application Type library
    Word 95 and prior wb70en32.tlb
    Excel 95 and prior xl5en32.olb
    PowerPoint 95 and prior PowerPoint.tlb
    Access 95 and prior msaccess.tlb
    Binder 95 binder.tlb
    Schedule+ sp7en32.olb
    Project pj4en32.olb
    Team Manager mstmgr1.olb
    Word 97 msword8.olb
    Excel 97 excel8.olb
    PowerPoint 97 msppt8.olb
    Access 97 msacc8.olb
    Binder 97 msbdr8.olb
    Graph 97 graph8.olb
    Outlook 97 msoutl8.olb
    Outlook 98 msoutl85.olb
    Word 2000 msword9.olb
    Excel 2000 excel9.olb
    PowerPoint 2000 msppt9.olb
    Access 2000 msacc9.olb
    Outlook 2000 msoutl9.olb
    Word 2002 msword.olb
    Excel 2002 excel.olb
    PowerPoint 2002 msppt.olb
    Access 2002 msacc.olb
    Outlook 2002 msoutl.olb

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • This paper introduces IBM Java on the IBM PowerLinux 7R2 server and describes IBM's implementation of the Java platform, which includes IBM's Java Virtual Machine and development toolkit.

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds