Link to home
Start Free TrialLog in
Avatar of pepr
pepr

asked on

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.
Avatar of jkr
jkr
Flag of Germany image

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---------------------------------
Avatar of pepr
pepr

ASKER

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?

ASKER CERTIFIED SOLUTION
Avatar of jkr
jkr
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pepr

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pepr

ASKER

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.
Avatar of pepr

ASKER

@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

Avatar of pepr

ASKER

Thanks again,
   Petr