You would be able to use Excel Objects from within C++ to do what you
want, but unfortunately C++ isn't really in my remit. Hope you manage
to find what you're looking for.
I figure it out, here is some of the code:
#include "ole2.h"
// main pointer for Excel
IDispatch * pExcelApplication = NULL;
// Workbooks collection
IDispatch * pExcelWorkbooks = NULL;
// Workbook collection
IDispatch * pExcelWorkbook = NULL;
int StartExcelServer (void)
{
// this code is somewhat from
http://support.microsoft.com/kb/216686
// and from
http://support.microsoft.com/kb/238610
// and from
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID (L"Excel.Application", & clsid);
if (FAILED (hr))
{
::MessageBox (NULL, "CLSIDFromProgID() failed", "Error", MB_ICONSTOP | MB_OK);
return false;
}
// see if we can connect to existing excel server and get idispatch
// NOTE: the process permission levels must be the same for this process
// and the excel process for GetActiveObject to work correctly.
// So, if running xyz.exe from visual studio then excel must
// be running as administrator also.
IUnknown * pIUnknown = NULL;
hr = GetActiveObject (clsid, NULL, (IUnknown**) & pIUnknown);
if (SUCCEEDED (hr))
{
// convert the iunknown pointer to an idispatch pointer
hr = pIUnknown -> QueryInterface (IID_IDispatch, (void**) & pExcelApplication);
// release the iunknown pointer since we dont need it anymore
pIUnknown -> Release ();
}
// if failed to talk to an existing excel then start server and get IDispatch...
if (FAILED (hr))
hr = CoCreateInstance (clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **) & pExcelApplication);
if (FAILED (hr))
{
::MessageBox (NULL, "Could not start Excel OLE Automation Server", "Error", MB_ICONSTOP | MB_OK);
return false;
}
// Make excel visible (i.e. app.visible = 1)
if ( ! FAILED (hr))
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
OLEMethod (DISPATCH_PROPERTYPUT, NULL, pExcelApplication, L"Visible", 1, x);
}
// Get Workbooks collection
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_PROPERTYGET, & result, pExcelApplication, L"Workbooks", 0);
pExcelWorkbooks = result.pdispVal;
}
return true;
}
// return a standard string with the file + sheet + current selected cell
std::string GetExcelCurrentSelection (void)
{
std::string selection = "";
// for OLE, will get something like '[EXPANDER-in.xls]Mat Bal'!F3
// first get the name of the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelApplication, L"ActiveWorkbook", 0);
if (result1.vt == VT_DISPATCH)
{
IDispatch *pDisp = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pDisp, L"Name", 0);
if (result2.vt == VT_BSTR)
{
selection += "\'[";
selection += _bstr_t (result2.bstrVal);
selection += "]";
}
pDisp -> Release ();
}
// get the name of the sheet
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelApplication, L"ActiveSheet", 0);
if (result2.vt == VT_DISPATCH)
{
IDispatch *pDisp = result2.pdispVal;
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pDisp, L"Name", 0);
if (result3.vt == VT_BSTR)
{
selection += _bstr_t (result3.bstrVal);
selection += "\'!";
}
pDisp -> Release ();
}
// get the selected cell(s) addresses
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelApplication, L"Selection", 0);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
// this will get the contents of the selected cell
// VariantInit ( & result);
// OLEMethod (DISPATCH_PROPERTYGET, & result, pDisp, L"Value", 0);
// int res_value = result.vt;
// this will get the address of the selected cell
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Address", 0);
if (result4.vt == VT_BSTR)
{
std::string absoluteReference = _bstr_t (result4.bstrVal);
int len = absoluteReference.size ();
for (int i = 0; i < len; i++)
{
if (absoluteReference
!= '$')
selection += absoluteReference ;
}
}
pDisp -> Release ();
}
return selection;
}
int ConnectToNotebook (std::string newNotebookName, int runInTestMode)
{
int tries = 0;
// set the error counter to zero each time
NumberOfErrors = 0;
buffer = newNotebookName;
int len = buffer.size ();
// remove the .OUT extension on the name if there is one
if (len > 4 && ! _strnicmp (&(buffer.c_str () [len - 4]), ".out", 4))
buffer.erase (len - 4);
// copy the new name in with a .XLS extension if not there already
len = buffer.size ();
if (len < 4 || 0 != _strnicmp (&(buffer.c_str () [len - 4]), ".xls", 4))
notebookName = buffer + ".xls";
else
notebookName = buffer;
notebookPrefix = buffer;
GetStartupDir ();
if ( ! MakeSureNotebookExists (notebookName))
return FALSE;
int ret = StartExcelServer ();
if ( ! ret)
{
buffer = "Can't start conversation with Excel.\n"
"Problem with either Excel or OLE automation.";
MessageBox (0, buffer.c_str (), "ERROR", MB_ICONSTOP | MB_TASKMODAL);
return false;
}
// if we got here then Excel is alive and ready to take input
// get the names of the currently open spreadsheets and see if this one is open already
// otherwise open the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelWorkbooks, L"Count", 0);
int numberOfWorkbooks = 0;
if (result1.vt == VT_I4)
numberOfWorkbooks = result1.intVal;
int workbookOpenAlready = false;
if (numberOfWorkbooks > 0)
{
for (int i = 0; i < numberOfWorkbooks && ! workbookOpenAlready; i++)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelWorkbooks, L"Item", 1, itemNumber);
if (result2.vt == VT_DISPATCH)
{
IDispatch *pDisp = result2.pdispVal;
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pDisp, L"Name", 0);
if (result3.vt == VT_BSTR)
{
// this will be the workbook name without the path
std::string workbookName = _bstr_t (result3.bstrVal);
// strip the path from the current workbook name
std::string noPathNotebookName = notebookName;
int lastSlash = notebookName.size ();
while (lastSlash >= 0 && notebookName [lastSlash] != '\\')
lastSlash--;
if (lastSlash >= 0)
noPathNotebookName.erase (0, lastSlash + 1);
if (workbookName == noPathNotebookName)
{
workbookOpenAlready = true;
// copy the dispatch pointer to the workbook pointer
pExcelWorkbook = pDisp;
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate", 0);
}
else
pDisp -> Release ();
}
}
}
}
// we need to open the spreadsheet file if not done already
if ( ! workbookOpenAlready)
{
VARIANT result;
VariantInit ( & result);
VARIANT fname;
fname.vt = VT_BSTR;
_bstr_t notebookNameBstr = _bstr_t (notebookName.c_str ());
fname.bstrVal = notebookNameBstr;
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbooks, L"Open", 1, fname);
// copy the dispatch pointer to the workbook pointer
pExcelWorkbook = result.pdispVal;
}
return TRUE;
}