Excel Automation
Hej,Under arbejde med Automation af Excel i C++ (har brugt eksemplet fra: http://support.microsoft.com/kb/216686), skal jeg indarbejde Automation i forbindelse med en UDF fra en xll. Bruger jeg koden fra linket virker min UDF, men resultatet er, at der åbnet en ny instance af Excel, tilføjes en ny workbook og værdier indsættes derefter i dette ark. Jeg ønsker imidlertid at data bliver indsat i det nuværende ark, altså det ark som brugeren har indtastet din UDF i. Hvorledes gøres dette?
Den relevante kode er:
// Initialize COM for this thread...
CoInitialize(NULL);
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
// Start server and get IDispatch...
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
// Make it visible (i.e. app.visible = 1)
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
}
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}
// Call Workbooks.Add() to get a new workbook...
IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"ActiveSheet", 0);
pXlBook = result.pdispVal;
}
// Create a 15x15 safearray of variants...
VARIANT arr;
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1; sab[0].cElements = 15;
sab[1].lLbound = 1; sab[1].cElements = 15;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
}
// Fill safearray with some values...
for(int i=1; i<=15; i++) {
for(int j=1; j<=15; j++) {
// Create entry value for (i,j)
VARIANT tmp;
tmp.vt = VT_I4;
tmp.lVal = i*j;
// Add to safearray...
long indices[] = {i,j};
SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
}
}
// Get ActiveSheet object
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
// Get Range object for the Range A1:O15...
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(L"A1:O15");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm);
pXlRange = result.pdispVal;
}
// Set range with our safearray...
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr);
// Wait for user...
::MessageBox(NULL, "All done.", "Notice", 0x10000);
// Release references...
pXlRange->Release();
pXlSheet->Release();
pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
VariantClear(&arr);
// Uninitialize COM for this thread...
CoUninitialize();