Ho to check the installe Excel capabilities programatically?

Posted on 2011-09-06
Last Modified: 2012-05-12
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,
Question by:pepr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
ID: 36489056
You are correct about the row and column limitations. Given that, why not export as xls for safety?
LVL 17

Assisted Solution

andrewssd3 earned 350 total points
ID: 36489115
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.

LVL 17

Expert Comment

ID: 36489138
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.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 86

Assisted Solution

jkr earned 100 total points
ID: 36489591
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.
LVL 29

Author Comment

ID: 36490134
@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 ;)
LVL 29

Author Comment

ID: 36490514
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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 36490784
You could simply check the xlApp.Activesheet.Rows.Count property. If greater than 65536, you can assume 2007 or later.
LVL 29

Author Comment

ID: 36490931
@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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 36491349
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++)

LVL 17

Accepted Solution

andrewssd3 earned 350 total points
ID: 36491627
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#
LVL 29

Author Comment

ID: 36491838
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 :)
LVL 85

Expert Comment

by:Rory Archibald
ID: 36491883
Not both? :)
LVL 29

Author Comment

ID: 36493854
@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 (about 4 KB).  Everything after compilation can be downloaded from (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 ;)
LVL 29

Author Comment

ID: 36493881
@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.
LVL 17

Assisted Solution

andrewssd3 earned 350 total points
ID: 36501316
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.
LVL 29

Author Comment

ID: 36502612
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.

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

691 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