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

Hi,

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,
    Petr

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.
LVL 30
peprAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkrCommented:
Here's a statement by a MS employee about that: http://social.msdn.microsoft.com/Forums/en-US/vbinterop/thread/0b98e92e-8f0c-4193-b058-1f460e86cec2 - you won't like it, though:

---------------------------------------------->8---------------------------------

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.

---------------------------------------------->8---------------------------------
peprAuthor Commented:
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?

jkrCommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

peprAuthor Commented:
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);
            m_spSheet->PutName(SheetName.c_str());

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,
                           subKey.c_str(),
                           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?
jkrCommented:
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...
peprAuthor Commented:
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.
peprAuthor Commented:
@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, 
                                    L"\x000c\x000c\x000c\x000c\x000c\x000c\x0003\x080c\x080c\x080c\x080c", 
                                    &Filename, &FileFormat, &Password, &WriteResPassword, &ReadOnlyRecommended,
                                    &CreateBackup, AccessMode, &ConflictResolution, &AddToMru, &TextCodepage,
                                    &TextVisualLayout);
    }

Open in new window

peprAuthor Commented:
Thanks again,
   Petr
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.