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;

Full code listing

#include <iostream>

#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

using namespace std;


int main()
{
	HRESULT hr = CoInitializeEx(0, COINIT_MULTITHREADED); 
	if (FAILED(hr)) 
	{
		cout << "Failed to initialize COM library. Error code = 0x"
		   << hex << hr << endl; 
		return hr;
	}

	// Create Excel Application Object pointer
	Excel::_ApplicationPtr pXL;

	if ( FAILED( pXL.CreateInstance( "Excel.Application" ) ) )
	{
		cout << "Failed to initialize Excel::_Application!" << endl;
		return 0;
	}
	
	// Open the Excel Workbook, but don't make it visible
	pXL->Workbooks->Open( L"C:\\dump\\book.xls" );
	pXL->Visible = false;

	// Access Excel Worksheet and return pointer to Worksheet cells
	Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;
	Excel::RangePtr pRange = pWksheet->Cells;

	// Read an Excel data cell. (Note Excel cells start from index = 1)
	double value = pRange->Item[1][1];

	// Write/modify Excel data cells + save. (reopen xls file to verify)
	pRange->Item[1][1] = 5.4321;
	pRange->Item[1][2] = 1.1211;
	pWksheet->SaveAs("C:\\dump\\book.xls");
	
	// Exit the Excel Com object
	pXL->Quit();	

	return 0;
}

4 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 ?

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>