Example of automating MS Excel

[Alex Bakaev [TeamB]]

This code prints Excel sheets. It determines if a sheet is empty first
(took me a while to figure out how to do that).
Hope this will help those who are struggling with automating Excel.
Alex

#include <vcl.h>
#pragma hdrstop
#if __BORLANDC__ >= 0x550
#include "excel_97.h"
#define CoApplication_ CoExcelApplication
#define Excel_tlb Excel_97
#define WorkbookPtr ExcelWorkbookPtr
#else
#include "excel_tlb.h"
#endif

//---------------------------------------------------------------------------
bool __fastcall DoExcel( LPCSTR file, int *count )
{
*count = 0;
bool started = false;
do {
try {
TCOM_Application ExlApp = CoApplication_::Create();
started = true;
try {
ExlApp.set_Visible( 0, true );
Excel_tlb::WorkbooksPtr pDoc = ExlApp.get_Workbooks();
if ( pDoc ) {

TVariant DocName( file );
TVariant FalseParam( false );
TVariant TrueParam( true );
TVariant pass( "password" );

Excel_tlb::WorkbookPtr wb = pDoc->Open( DocName,
FalseParam,
FalseParam, TNoParam(), pass, pass );
if ( wb ) {
Excel_tlb::SheetsPtr sheets = wb->Sheets;
if ( sheets ) {
int totalSheets = sheets->Count;
for( int i = 1; i <= totalSheets; i++ ) {

_Worksheet *pWorkSheet = 0;
TVariant idx( i );
sheets->get_Item( idx, (LPDISPATCH*)&pWorkSheet
);

if ( pWorkSheet ) {

_WorksheetDisp sh( pWorkSheet );

// see if we have an empty sheet on our hands
BSTR addr = L"Good";
TVariant val = 5;
Excel_tlb::RangePtr Cells = sh.Cells;

if ( Cells ) {
Excel_tlb::RangePtr lastCell =
Cells->SpecialCells( xlCellTypeLastCell
);
if ( lastCell ) {
addr = lastCell->get_Address(
TNoParam(),
TNoParam(), xlA1, TNoParam(),
TNoParam() );

val = lastCell->get_Value();
}
}
if ( WideString( addr ) != WideString(
L"$A$1" ) ||
val.vt ) { // nope, not empty
sh.PrintOut();
if ( !sh.get_excepinfo().wCode )
++(*count);
} else {
// see if there are any pictures
PicturesPtr pics = sh.Pictures();
if ( pics.IsBound() && pics->Count ) {
sh.PrintOut();
if ( !sh.get_excepinfo().wCode )
++(*count);
} else {
Excel_tlb::ShapesPtr shapes =
sh.Shapes;
if ( shapes.IsBound() && shapes->Count
) {
sh.PrintOut();
if ( !sh.get_excepinfo().wCode )
++(*count);
}
}
}
}
}
}
TVariant noSave( xlDoNotSaveChanges );
wb->Close( noSave );
}
pDoc->Close();
}
ExlApp.Quit();
}
catch( ... ) {
if ( ExlApp )
ExlApp.Quit();
return false;
}
}
catch( EOleException &e ) {
if ( e.ErrorCode == CO_E_SERVER_EXEC_FAILURE )
continue; // for some reasons out of proc servers fail to
start
return false;
}
} while ( !started );
return true;
}
//---------------------------------------------------------------------------