Dear experts,
Could anybody advise me how to create a pivot table in Excel using C++ (not using MFC or #import)? In particular, how Range should be defined to be properly passed into IDispatch::invoke(...) function on a stage of PivotCach object creation? In VB it looks like:
Dim aWrkBook As Workbook
Set aWrkBook = ActiveWorkbook
Dim pvtCaches As PivotCaches
Set pvtCaches = aWrkBook.PivotCaches
Dim pvtcache As PivotCache
Set pvtcache = pvtCaches.Create(xlDatabase, "ComponentsByAssy!R1C1:R2301C68", xlPivotTableVersion15)
Dim pvtTable As PivotTable
Set pvtTable = pvtcache.CreatePivotTable("TOTAL!R1C1", "PivotTable1", xlPivotTableVersion15)
Note: I especially divided operations for better visibility and comparison with C++ code.
In my interfaceMsExcel C++ class the function for Pivot Table creation currently looks as follows:
void interfaceMsExcel::createPivotTable(char *sourceDatasheetName,
char *sourceCellsRange,
char *destDatasheetName,
char *destCellsRange,
char *pivotTableName)
{
if (pXlApp == NULL) return;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveWorkbook", 0);
if ((pXlActiveWorkbook = result.pdispVal) == 0)
{ fl_alert("Cannot get ActiveWorkbook");
return;
}
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlActiveWorkbook, L"PivotCaches", 0);
if ((pXlPivotCaches = result.pdispVal) == 0)
{ fl_alert("Cannot get PivotCaches");
return;
}
VARIANT sourceType, version;
VariantInit ( & sourceType);
sourceType.vt = VT_I4;
sourceType.intVal = xlDatabase;
VariantInit ( & version);
version.vt = VT_I4;
version.intVal = xlPivotTableVersion15;
VARIANT sourceRange;
VariantInit ( & sourceRange);
sourceRange.vt = VT_DISPATCH;
sourceRange.pdispVal = pXlRange;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlPivotCaches, L"Create", 3, sourceType, sourceRange, version);
if ((pXlPivotCache = result.pdispVal) == 0)
{ fl_alert("Cannot create PivotCache");
return;
}
}
Some variables undeclared in the function defined as class members, for example pXlRange defined earlier and used in another functions with success):
class interfaceMsExcel
{
private: VARIANT arr;
IDispatch *pXlApp;
IDispatch *pXlWindow;
IDispatch *pXlBooks;
IDispatch *pXlBook;
IDispatch *pXlActiveWorkbook;
IDispatch *pActiveSheet;
IDispatch *pXlSheet;
IDispatch *pXlSheets;
IDispatch *pXlRange;
IDispatch *pXlColumns;
...
IDispatch *pXlPivotCaches; IDispatch *pXlPivotCache;
public:
void createPivotTable(char *sourceDatasheetName, char *sourceCellsRange, char *destDatasheetName, char *destCellsRange, char *pivotTableName);
}
What I have tried:
I created my own wrapper class taking into account MSDN article "How to automate Excel from C++ without using MFC or #import". I can successfully transfer large amount of data to Excel with formatting.
Now I need to create a Pivot Table in the same Workbook. And I have a problem. I know that parameter containing Range shall be defined as VARIANT and I do not know in details how to define it so it could be properly recognized by excel interface. I tried to set the parameter as VT_BSTR type and receive error: "Parameter cannot be coerced"; when I try to set parameter with VT_DISPATCH type I receive error: "The application raised an Exception".
In MSDN this parameter for VB described as Range object. How properly define it in C++?
Thank you in advance for your time.