?
Solved

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

Posted on 2011-10-05
8
Medium Priority
?
809 Views
Last Modified: 2013-11-17
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.
0
Comment
Question by:pepr
  • 5
  • 3
8 Comments
 
LVL 86

Expert Comment

by:jkr
ID: 36917088
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---------------------------------
0
 
LVL 29

Author Comment

by:pepr
ID: 36917197
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?

0
 
LVL 86

Accepted Solution

by:
jkr earned 2000 total points
ID: 36917231
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)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Author Comment

by:pepr
ID: 36917468
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?
0
 
LVL 86

Assisted Solution

by:jkr
jkr earned 2000 total points
ID: 36917693
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...
0
 
LVL 29

Author Comment

by:pepr
ID: 36920214
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.
0
 
LVL 29

Author Comment

by:pepr
ID: 36960817
@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

0
 
LVL 29

Author Closing Comment

by:pepr
ID: 36960825
Thanks again,
   Petr
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

862 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