Export to MS Excel programmatically from our application... What related to MS Office must be distributed?

Posted on 2011-10-05
Last Modified: 2013-11-17

It is difficult for me to explain the problem with few sentences.  Basically, we have the application that uses Excel components to export some data to its .xls format.  Earlier, we used the Office 97 interface; now the Office 2007 interface is used.  It happens that the user may have older MS Office installed.  What must be distributed with the application to make sure that the export works?

More details.  The application is written in native C++ (ATL, WTL, COM), and the interface code source was generated via:

#import "C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\MSO.DLL"\
        rename("DocumentProperties", "DocumentPropertiesMSO")\
        rename("SearchPath", "SearchPathMSO")\
        rename("RGB", "RBGMSO")

#import "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
#import "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE"\
        rename("DialogBox", "DialogBoxXL")\
        rename("RGB", "RBGXL")\
        rename("ReplaceText", "ReplaceTextXL")\
        rename("CopyFile", "CopyFileXL")\
        exclude("IFont", "IPicture")

Open in new window

In other sources the headers are included via:

#include "..\Office12\mso.tlh"
#include "..\Office12\vbe6ext.tlh"
#include "..\Office12\Excel.tlh"

Open in new window

The export works fine when having MS Office 2007 installed.  The other tested and working configuration had MS Office XP (i.e. 2002) with Compatibility Pack installed (even the .xlsx format works here).  However, the MS Office 2000 is installed (without any extra pack), the application crashed.  It is not that surprising for me as there is probably nothing like mso.dll and the other components that are expected to be there.  So, the question is...

Is there any recommended package or installer to be distributed for the MS Office solution?

Thanks for your time and experience,

P.S.  I am sending this explicitly to C++ zone as the generated interface may be different than say working from Visual Basic.  Anyway, the problem may be language independent.  Because of this I have chosen the MS Excel zone as the primary one.
Question by:pepr
    LVL 86

    Expert Comment

    Here's a statement by a MS employee about that: - you won't like it, though:


    As far as I'm aware, automation requires the product to be installed.   As the name implies it is simply automating the use of the product.

    The DLL's required would be pretty well the product itself and these are not redistributable.   So the general point here is you can write office automation apps but they require office to be installed to work - no way round that as far as I am aware.

    LVL 28

    Author Comment

    I know that the MS Excel must be installed.  The user also knows it.  This is not the situation to be worked around.  I do not want to export to Excel format without MS Excel being installed.

    The problem is that the earlier application that used the Excel 97 interface worked also with the newer Excels (the old interface is still supported by the newer Excels).  However, the new interface is supported by the older Excel apparently only in some cases -- e.g. when the MS Office Compatibility Pack is installed.  

    I am probably able to test the presence of the compatibility pack.  And the Compatibility Pack is really needed when I want to use the newer format, like .xlsx.  However, the Compatibility Pack is probably needed also in the case when I want to export the data in the format that is supported by both older and newer Excels (.xls).  

    If I understand the Compatibility Pack principle, it installs the components that implement also the interface of the newer Excel for the older Excel.  The question could possibly be reformulated: Is the instalation of the Compatibility Pack the only way how to make the older Excel work via the newer interface?

    LVL 86

    Accepted Solution

    Well, the only alternative I can see not using the Compatibility Pack probably would be to upgrade Excel ;o)

    Seriously, since COM allows you to 'QueryInterface()' to find out what interfaces Excel supports, this should be quite transparent (unless I am missing a point) - so if wither the CP or a newer version is available, use the new interfaces, if not, don't (and inform the user via an error message)
    LVL 28

    Author Comment

    I knew it is difficult to explain ;) No problem...

    The generated interface looks like:

    struct __declspec(uuid("000c03d7-0000-0000-c000-000000000046"))
    /* interface */ IConverter;
    typedef long MsoRGBType;
    // Smart pointer typedef declarations
    _COM_SMARTPTR_TYPEDEF(IAccessible, __uuidof(IAccessible));
    _COM_SMARTPTR_TYPEDEF(_IMsoDispObj, __uuidof(_IMsoDispObj));
    _COM_SMARTPTR_TYPEDEF(_IMsoOleAccDispObj, __uuidof(_IMsoOleAccDispObj));

    Open in new window

    Using the interface, I can create the instance of Excel using the code like:

        HRESULT hr = m_spExcel.CreateInstance(OLESTR("Excel.Application"));

    Open in new window

    and then I can use the Excel component like this (testing omitted for brevity here):

                m_spBook = m_spExcel->GetWorkbooks()->Add();
                m_spExcel->PutSheetsInNewWorkbook(lcid, 1);
                m_spSheet = m_spBook->GetSheets()->GetItem(1L);

    Open in new window

    In other words, I am not testing if the interface exists or not.  I know that something must be done to spring the new interface to life.  

    I can test the following that is probably related to the compatibility pack existence:

    RegOpenKeyEx(HKEY_LOCAL_MACHINE,  TEXT("Software\\Classes\\OfficeCompatible.Application\\CLSID"), 0, KEY_READ, &hKey);
    RegQueryValueEx(hKey, TEXT(""), NULL, &ValueType, buf, &buflen);
            string subKey("Software\\Classes\\CLSID\\");
            subKey += reinterpret_cast<char*>(buf);
            subKey += "\\InprocServer32";
            res = RegOpenKeyEx(HKEY_LOCAL_MACHINE,
                               0, KEY_READ, &hKey);

    Open in new window

    i.e. to get the mso.dll that probably implements the interface.  After getting the version of the file, I can obtain information for my log file like this:

    MS Excel:	10.0.6871; C:\Program Files\Microsoft Office\Office10\EXCEL.EXE
    MS Word:	10.0.6866; C:\Program Files\Microsoft Office\Office10\WINWORD.EXE
    MSO Compat.:	12.x.xxxx; C:\Program Files\Common Files\Microsoft Shared\Office12\MSO.DLL

    Open in new window

    (I do not have the exact last line just now, but I did observe the higher version that the Excel version -- and then it worked.)

    However, the CompatibilityPack has the form of some .exe.  Do you know about anything like .msi, or should I rely only on asking the user to launch the Compatibility Pack .exe?
    LVL 86

    Assisted Solution

    Hmm, after a quick search on MSDN, it appears that all these are in fact .exe files only - you could either start it yourself or indeed ask the user to do that manually, but I am afraid this is just a matter of preference...
    LVL 28

    Author Comment

    Thanks for your research.  I did not find anything else either.  I will close the question a bit later for the case anyone had anything to add.
    LVL 28

    Author Comment

    @jkr: You are right.  The #import commands generates the wrappers to QueryInterface()... This way I can do the same by just copying the older generated wrapper that uses the older interface and give it its unique name.  The problem was solved via detecting the Excel version (i.e. 8, 9, 10, 11, ...) and calling the appropriate function based on what is available -- like this:

        HRESULT SaveAs97_(Excel::_WorkbookPtr spBook, 
                          const _variant_t & Filename, 
                          const _variant_t & FileFormat, 
                          const _variant_t & Password, 
                          const _variant_t & WriteResPassword, 
                          const _variant_t & ReadOnlyRecommended, 
                          const _variant_t & CreateBackup, 
                          enum Excel::XlSaveAsAccessMode AccessMode, 
                          const _variant_t & ConflictResolution, 
                          const _variant_t & AddToMru, 
                          const _variant_t & TextCodepage, 
                          const _variant_t & TextVisualLayout) 
            return _com_dispatch_method(spBook, 0x11c, DISPATCH_METHOD, VT_EMPTY, NULL, 
                                        &Filename, &FileFormat, &Password, &WriteResPassword, &ReadOnlyRecommended,
                                        &CreateBackup, AccessMode, &ConflictResolution, &AddToMru, &TextCodepage,

    Open in new window

    LVL 28

    Author Closing Comment

    Thanks again,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now