Solved

Ho to check the installe Excel capabilities programatically?

Posted on 2011-09-06
16
292 Views
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,
    Petr
0
Comment
Question by:pepr
  • 7
  • 4
  • 4
  • +1
16 Comments
 
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?
0
 
LVL 17

Assisted Solution

by:andrewssd3
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.

0
 
LVL 17

Expert Comment

by:andrewssd3
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.
0
 
LVL 86

Assisted Solution

by:jkr
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.
0
 
LVL 28

Author Comment

by:pepr
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 ;)
0
 
LVL 28

Author Comment

by:pepr
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.
0
 
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.
0
 
LVL 28

Author Comment

by:pepr
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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++)

Regards,
Rory
0
 
LVL 17

Accepted Solution

by:
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#
0
 
LVL 28

Author Comment

by:pepr
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 :)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36491883
Not both? :)
0
 
LVL 28

Author Comment

by:pepr
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 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:

StdAfx.h
#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()
{
    CoInitialize(0);

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

    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.
    spChartObj->Activate();

    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->Close(); 
    spBook = NULL;
    spWorkbooks = NULL;

    spExcel->Quit();
    spExcel = NULL;

    CoUninitialize();
    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 ;)
0
 
LVL 28

Author Comment

by:pepr
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.
0
 
LVL 17

Assisted Solution

by:andrewssd3
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.
0
 
LVL 28

Author Comment

by:pepr
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.
    Petr
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now