Ho to check the installe Excel capabilities programatically?

pepr used Ask the Experts™
Hi experts,

The question is namely for MS Excel experts.  I have implemented the data export to the Excel format via the Excel 2007 interface.  It works fine.  The problem is that the application will be installed on computers with possibly older versions of Excel.  I know how to check the version of the installed Excel; however, some older versions of the Excel may have installed the Microsoft Office Compatibility Pack that allows export to the newer formats.  How can I be sure that the chosen form of the export can be done on the computer?

To be more specific.  How can I check that the SaveAs method will accept saving the document in .xlsx format?

Also, the Excel 2007 weakened the limitation to the number of columns and rows.  I assume that the earlier versions of Excel still have the more strict limitation even if they support the newer format (xlsx).  Can you confirm that?

Thanks for your time and experience,
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
You are correct about the row and column limitations. Given that, why not export as xls for safety?
Top Expert 2011
I can only answer some of this question as I don't currently have access to a pre-2007 machine without the compatibility pack.

It is possible to use Workbook.Save as with the file format of 51 (which is the value of XlFileFormat.xlOpenXMLWorkbook), and it works on my test machine that has the compatibilty pack.  I assume it would raise an error that you could handle on a machine with no compat. pack. However, you also have the problem that you will need to check whether the workbook has macros or not, otherwise you would lose them by saving in .xlsx format.  This is possible of course, but I wonder if you would be better to save in the .xls format exclusively in pre-2007 versions, regardless of whether the compat. pack is installed.

As for the number of rows and columns, yes the limitations still apply, and you could not save more than the old maximums (65536 / 256) on a pre-2007 machine. Application.Rows.Count and Appliaction.Columns.Count give you the maximum for the installed version on any machine.

Top Expert 2011

Sorry crossed over with rorya's comment - looks like we agree about saving .xls format - it's much safer and you wouldn't have to worry about macros.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Top Expert 2012
I have to admit that this is just an idea, but if I'd have to do that, I'd try to get an automation interface to Excel and 'QueryInterface()' on that to check the support of interfaces that are specific to the respective Compatibility Pack you want to check for.


@roya and andrewssd3: The main motivation for using the Excel 2007 type library (the #import generated .tli and .tlh files in C++) was to avoid column and row number limitation requested by our customer.  (Frankly, I would understand that they would like to use more columns.  Actually, they complained about the limited number of rows.  Don't ask me, how such Excel files may be useful ;)  When exporting to .xls, the limitation to 256 columns and 64 K row is there even when having Excel 2007.  This means that they really need the .xlsx plus the Excel 2007+.

@jkr:  Using a specific interface was my first idea.  The problem is that the type library in the form of the C++ sources is generated independently on the required interface.

I am not sure, but if I recall correctly, I failed when trying to instantiate the Excel.Application.8 with Excel 2007.  I could make a mistake that time.  Actually, I wanted to postpone that solution for later.  My original idea was to have Excel 2007 support first.  After that I wanted to check whether the new mechanism can be used.

I was quite surprised that the export based on Excel 2007 works also on the machine with Office 2003 (of my colleague).  Only then I have learned about the compatibility pack.

So far, so good.  Actually the problem is what happens if the compatibility pack is not installed.  I need to check it in advance, and not only after the customers complains ;)


Clarification for andrewssd3's "need to check whether the workbook has macros or not".  I am generating a completely new document.  This way I know exactly what is exported.  Actually, the operations are the same subset for both .xls and .xlsx documents.  The only and the major reason is to avoid the row/column limitation.

Thinking more about the problem, the version of the generated document is likely to be related to the workbook object, is it?  I am not the Excel expert.  Could the capability be checked when opening the document?  The truth is that I know the wanted format in advance, but the format is passed only to the SaveAs method.
Most Valuable Expert 2011
Top Expert 2011

You could simply check the xlApp.Activesheet.Rows.Count property. If greater than 65536, you can assume 2007 or later.


@rorya: I have no problem to detect the Excel 2007 and better.  This way I can check whether rows/columns are out of range.  I do not have a problem to check the older versions of Excel either.  The problem is that I was able to test the export only to Excel 2003 with the compatibility pack installed.  I do not know whether it works (even to the older .xls format) with the older Excel without the compatibility pack (within the old row/column range).  It is clear that it will work with Excel 2007 or better.  It is not clear how to determine whether the older Excel will allow the export.

Sorry for not being totaly clear.  I am also searching for what else should I know to solve the problem.
Most Valuable Expert 2011
Top Expert 2011

Without knowing how you are exporting, I'm not sure how anyone can answer that (and I won't be able to anyway, since I don't speak C++)

Top Expert 2011
You don't seem to have any choice but to get a test machine (or VM) that does not have the comp. pack installed.

This may be a silly question, but why use C++?  All of this stuff would be easier to manage and test with VBA, VB.NET, C#


I will post the source code tomorrow -- the tiny example.  Actually, you can see the idea attached to my earlier question http:Q_27282284.html plus the solution below.

P.S. When looking at my question... I had to be tired or completely drunk :)
Most Valuable Expert 2011
Top Expert 2011

Not both? :)


@roya: Frankly, I almost do not remember the last time when I was drunk.  (What day is today? :))

Full sources and the project files for the Visual Studio 2008 (without any binaries) can be downloaded from http://www.skil.cz/tmp/ExcelChartFailureSource.zip (about 4 KB).  Everything after compilation can be downloaded from http://www.skil.cz/tmp/ExcelChartFailureAll_8MB.zip (about 8 MB, you know, all the .tli, .tlh, .ncb, .suo and everything untouched, both debug and release; binaries are about 500 KB with linked everything inside [debug] or 70 KB with Multi-Threading DLL [release]).

There are only two specific C++ sources:

#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

... and the main.cpp (heavy checks removed for the sake of brevity):

#include "StdAfx.h"
#include <cassert>

using namespace std;

int main()

    // Excel 2007 should be tested.
    Excel::_ApplicationPtr spExcel;
    HRESULT hr = spExcel.CreateInstance(OLESTR("Excel.Application"));  

    Excel::WorkbooksPtr spWorkbooks = spExcel->GetWorkbooks();
    Excel::_WorkbookPtr spBook = spWorkbooks->Add();
    Excel::_WorksheetPtr spSheet = spBook->GetSheets()->GetItem(1L);
    // Create the column of 3 cells with integer values.
    spSheet->Range["A1"]->Value2 = 1;
    spSheet->Range["A2"]->Value2 = 2;
    spSheet->Range["A3"]->Value2 = 3;

    // Get the range of the source data for the chart below.
    Excel::RangePtr spRData = spSheet->GetRange("A1", "A3");
    assert(spRData != NULL);

    // Now add the chart below the data. The chart collection object.
    Excel::ChartObjectsPtr spChartObjs(spSheet->ChartObjects());
    assert(spChartObjs != NULL);

    // Creating the new chart container.
    Excel::RangePtr spR = spSheet->GetRange("A5", "E10");
    Excel::ChartObjectPtr spChartObj(
        spChartObjs->Add(spR->Left, spR->Top, spR->Width, spR->Height));
    assert(spChartObj != NULL);

 * The following is the earlier code that failed during SetSourceData().
 *    // Get the chart that belongs to the chart container.
 *    Excel::_ChartPtr spChart(spChartObj->GetChart());
 *    assert(spChart != NULL);
 *    // Set the source data -- it fails with 0xC0000005.  Try to comment it out
 *    // to see that everything else works.
 *    spChart->SetSourceData(spRData, Excel::xlColumns);
 *  Apparently, it depends on how the spChart is obtained because the following
 *  works.  The question is:  Why?

    // Activate the object container and get the active chart.

    Excel::_ChartPtr spChart(spBook->GetActiveChart());
    assert(spChart != NULL);

    // Now the same SetSourceData() works. Why?
    spChart->SetSourceData(spRData, Excel::xlColumns);

//************ the rest is only saving the document and closing/releasing the objects.    
    // Save the document.
    _variant_t vtFileName(OLESTR("c:\\tmp\\mytab.xlsx"));
    //_variant_t vtFileName(OLESTR("c:\\tmp\\mytab.xls"));
    spBook->SaveAs(vtFileName,                   // Filename
                   static_cast<long>(Excel::xlOpenXMLWorkbook),      // FileFormat
                   //static_cast<long>(Excel::xlWorkbookNormal),      // FileFormat
                   vtMissing,                    // Password
                   vtMissing,                    // WriteResPassword
                   vtMissing,                    // ReadOnlyRecommended
                   vtMissing,                    // CreateBackup
                   Excel::xlExclusive,           // AccessMode
                   static_cast<long>(Excel::xlLocalSessionChanges), // ConflictResolution
                   false,                        // AddToMru
                   vtMissing,                    // TextCodepage
                   vtMissing                     // TextVisualLayout
    // Release and close everything.
    spSheet = NULL;
    spBook = NULL;
    spWorkbooks = NULL;

    spExcel = NULL;

    return 0;

Open in new window

All you can suggest better subquestions to be solved if you think it should be solved separately.  I will be glad to give you extra points ;)


@andrewssd3: I am just learning the C#.  But I am willing to try your suggestions.  Suggest how the extra question for the purpose should look like, please.
Top Expert 2011
Not sure what else to suggest.  As I said before why don't you just always save as .xls on pre-2007 machines, or try to SaveAs with file format 51 and an extension of .xlsx, and if an error is raised, revert to saving as .xls in the default format.

The only other thing I can think is that you could search the registry for a key that identifies the Compatibility Pack.  As I said I don't have a test machine to check this out, but on my 2007 machine there is a section called HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Common\InstalledPackages which lists installed packages.  You would have to search the Key under the relevant Office version - it might be obvious if you compare registry entries on machine with and without the compatibility pack.  I think they refer to the comp. pack sometimes as FileConverters as well.


Yes, I can be strict and allow the xlsx only when Office 2007 is installed.  The main motivation was to allow more rows and it does not work withou Office 2007 anyway.

On the other hand, I do not know what else may other customers lead to decision that they want .xlsx.

I am going to install Virtual PC plus the Win XP and some old Office to learn.

Thanks all for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial