How to interface with Excel in C++

Interfacing Excel in C++ is task that I needed to overcome recently, so I thought I would post some code and instructions on the said topic. Some online articles that I found to be useful include the following:

A Brief Introduction to C++ and Interfacing with Excel
Accessing Excel Spreadsheets via C++
Programming Excel COM Objects in C++

Without further ado, or huge swathes of boring theory, here are the steps needed to at least get you interfacing with Microsoft Excel via C++:

1: Ensure pre-requisite files are installed and located

We must ensure that a number of Microsoft libraries and their file locations have been installed and located, namely MSO.DLL, VBE6EXT.OLB and EXCEL.EXE. Make sure that you have a suitable version of Microsoft Excel installed, any version should do. In this example, Excel 2003 was used.

The locations of these files will obviously differ from machine to machine, so the first step is to determine their whereabouts. In Windows 7 for example, I recommend using Windows Explorer search facility. To locate (say) ‘VBE6EXT.OLB‘, click on the ‘Computer’ icon at the left, and in the edit box at the upper right simply enter ‘VBE6EXT.OLB‘ and let Explorer do the rest:

Right-click on the file and select ‘Open file location’. If you then click inside the file location edit box, this will give you the location you need, which you can then copy and paste:

In my example, the location of the ‘VBE6EXT.OLB‘ is
"C:\Program Files (x86)\Common Files\microsoft shared\VBA\VBA6\\VBE6EXT.OLB"

Do the same for the MSO.DLL and EXCEL.EXE files and you are then in a position to proceed to the next step.

2. Set up the Microsoft libraries

In order to use Excel functionality from within a C++ program we use the Microsoft Component Object Model (COM). The required import libraries are shown below and need to be included in any C++ application that interfaces with Excel.

Just use the file directories you found on your own computer for each #import, remembering to replace single backslashes (‘\’) with double backslashes (‘\\’), since the single backslash is classed as a special character:

#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE11\\MSO.DLL" \
    rename( "RGB", "MSORGB" )

using namespace Office;

#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"

using namespace VBIDE;

#import "C:\\Program Files (x86)\\Microsoft Office\\OFFICE11\\EXCEL.EXE" \
    rename( "DialogBox", "ExcelDialogBox" ) \
    rename( "RGB", "ExcelRGB" ) \
    rename( "CopyFile", "ExcelCopyFile" ) \
    rename( "ReplaceText", "ExcelReplaceText" ) \
	exclude( "IFont", "IPicture" ) no_dual_interfaces

rename is used to change certain strings in the import library to avoid clashes with other libraries containing functions, variables or classes with the same name. exclude prevents the import of specified items. no_dual_interfaces must also be included for the correct functioning of the Excel application function.

3. Use the Excel Object Model in your C++ code

The Excel Object Model contains a huge number of functions and objects. We will concentrate on just a few of those required to perform this tasks of reading from and writing to the Excel spreadsheet. Declaring an Excel Application Object pointer is simple enough:

Excel::_ApplicationPtr pXL;

Once created, we can use this pointer to open for reading and writing our Excel Workbook of choice:

pXL->Workbooks->Open( L"C:\\dump\\book.xls" );

Set the Visible parameter depending on whether you would like the Excel spreadsheet to be displayed or not:

pXL->Visible = false;  

This is how we can access the active Excel Worksheet and the cells within it:

	Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;
	Excel::RangePtr pRange = pWksheet->Cells;

To read the values of individual cells, simply use the Excel::RangePtr pointer above, remembering that in Excel cells must start from index = 1:

double value = pRange->Item[1][1];

While writing and modifying individual cells is just as straightforward:

pRange->Item[1][1] = 5.4321;

For example, I use the following simple Excel worksheet with a few modified cells:

ExcelCpp1

I then run the example code in order to read the cell values, modify them and save the spreadsheet respectively:

ExcelCpp2

Upon re-opening the spreadsheet “book.xls” notice how the cells have been updated and saved:

ExcelCpp3

Full code listing

#include <iostream>

// Make sure your file paths for MSO.DLL, VBE6EXT.OLB and EXCEL.EXE are 100% correct
// For 32-bit systems the file path is usually "C:\\Program Files\\..."
// For 64-bit systems the file path is usually "C:\\Program Files (x86)\\..."

// #import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE11\\MSO.DLL"
#import "C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE11\\MSO.DLL" \
	rename( "RGB", "MSORGB" ) 
 
using namespace Office;

//#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"  
#import "C:\Program Files\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"  
      
using namespace VBIDE;  
      
// #import "C:\\Program Files (x86)\\Microsoft Office\\OFFICE11\\EXCEL.EXE"   
#import "C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE" \
	rename( "DialogBox", "ExcelDialogBox" ) \
    rename( "RGB", "ExcelRGB" ) \
    rename( "CopyFile", "ExcelCopyFile" ) \
    rename( "ReplaceText", "ExcelReplaceText" )

int main()  
{  
    HRESULT hr = CoInitializeEx(0, COINIT_MULTITHREADED);   
    if (FAILED(hr))   
    {  
        std::cout << "Failed to initialize COM library. Error code = 0x"  
            << std::hex << hr << std::endl;   
        return hr;  
    }  
      
    // Create Excel Application Object pointer  
    Excel::_ApplicationPtr pXL;  
      
    if ( FAILED( pXL.CreateInstance( "Excel.Application" ) ) )  
    {  
        std::cout << "Failed to initialize Excel::_Application!" << std::endl;  
        return 0;  
    }  
          
    // Open the Excel Workbook, but don't make it visible  
    pXL->Workbooks->Open( L"C:\\dump\\book.xls" );  
    //pXL->Visible[ 0 ] = false;   // sometimes generates error?
	pXL->PutVisible(0, FALSE);
      
    // Access Excel Worksheet and return pointer to Worksheet cells  
    Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;  

	pWksheet->Name = L"Sheet1";

    Excel::RangePtr pRange = pWksheet->Cells;  

    // Read an Excel data cell. (Note Excel cells start from index = 1)
    double value1 = pRange->Item[1][1];
	std::cout << "Value in CELL [1][1] = " << value1 << std::endl;
	double value2 = pRange->Item[1][2];
	std::cout << "Value in CELL [1][2] = " << value2 << std::endl;

    // Write/modify Excel data cells + save. (reopen xls file to verify)
	std::cout << std::endl;
	std::cout << "Modifying Excel cell values..." << std::endl;
	std::cout << std::endl;
    pRange->Item[1][1] = 5.4321;  
    pRange->Item[1][2] = 1.1211;

    // Output new value
	value1 = pRange->Item[1][1];
	std::cout << "New value in CELL [1][1] = " << value1 << std::endl;
	value2 = pRange->Item[1][2];
	std::cout << "New value in CELL [1][2] = " << value2 << std::endl;

    // Switch off alert prompting to save as 
	pXL->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_FALSE );

    // Save the values in book.xml and release resources
    pWksheet->SaveAs( "C:\\dump\\book.xls" );
    pWksheet->Release();

    // And switch back on again...
	pXL->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_TRUE );  

    pXL->Quit();

    return 0;  
}  

One final thing – please be aware that any existing Excel processes still running in the background must be closed down before running this program, otherwise errors are likely to occur. For example you may be debugging this program and then decide to exit before pXL->Quit() is called:

ExcelCpp4

13 Comments

  1. Is it possible to do these import statements dynamically, so that it could be made to work for different versions of excel.

    Is it possible to do something like LoadLibrary

  2. Im trying to use this code but there are some errors such as” illegal escape sequence” at this line
    #import “C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE11\\MSO.DLL” \
    rename( “RGB”, “MSORGB” )
    note : I put 15 instead of 11
    any help ?

  3. thanks happyuk. I am using VS 2012 and Office 365 and it doesn’t work. In my other laptop it works very well with VS 2012 and Office 2010. Can anyone please tell me if it works with Office 2013?

  4. Pingback: Quora

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>