How to access cell properties in arrays for automation?

[Daniel Kim]

Hi

I am trying to create an automation program for Excel.
I have found this article
http://support.microsoft.com/kb/216686
and everything works fine.

But there's one problem. I need to access "ID" property of a cell, not just
a "value".
For "value" property, I can read and write with array, so it is done in few
seconds with millions of data.
But for "ID" property, I can only access one cell at a time, so I have to
play with loop things, and it is toooooooo slow.
If I set range and use it for getting "ID" property, it fails with code
"0x80020005".
Is there any way to access with array for excel properties?

Thanks in advance.

With BR,
Daniel Kim

 

[WhytheQ]

Hello Daniel,

Looping through cells is generally pretty quick: have you got a sample
of your code please?
Are you switching screenupdating off?

Regards
Jason.

 

[Daniel Kim]

Hello Jason

Thanks for the reply.
Yes, as you said, just manipulating the cells are not that slow.
The problem is, I have to set the range every time I move the cells.

Here is my code:

/////////////////////////////////////////////////////////////////////////////////////////////////
/*
I'm pretty much sure you already know the wrapper function AutoWrap(), so
I'm skipping it
*/
// Initialize COM for this thread...
CoInitialize(NULL);

// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

if(FAILED(hr)) {

::MessageBox(NULL, TEXT("CLSIDFromProgID() failed"), TEXT("Error"),
0x10010);
return -1;
}

// Start server and get IDispatch...
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch,
(void **)&pXlApp);
if(FAILED(hr)) {
::MessageBox(NULL, TEXT("Excel not registered properly"),
TEXT("Error"), 0x10010);
return -2;
}

// 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"Add", 0);
pXlBook = result.pdispVal;
}
// Get ActiveSheet object
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}

//Here starts the code cause problem
//It just iterates 30,000 times
IDispatch *pXlRange2 = NULL;
for(DWORD i=0;i<30000;i++)
{
if(pXlRange2 != NULL)
{
pXlRange2->Release();
pXlRange2 = NULL;
}

{
VARIANT parm;
parm.vt = VT_BSTR;
//This is just a sample code, so I didn't actually move
the cells
parm.bstrVal = ::SysAllocString(L"A1:A1");

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm);

pXlRange2 = result.pdispVal;
}
VARIANT t;
t.vt = VT_I4;
t.lVal = 1;
// Set range with our safearray...
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange2, L"ID", 1, t);
}

//////////////////////////////////////////////////////////////////////////////////////////////////

Please advice.
Thank you.
With BR,
Daniel Kim

 

[Daniel Kim]

As you can see, I am calling
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
for every iteration.
Since this code is just a sample to find out how long it would take, it does
not actually changes the range (always A1:A1), but I need to change it every
time, like A1:A1 for now and A2:A2 next time.
Is there any way to change this range, other than calling AutoWrap function
every time?
I have tried to retrieve and write data on the same cell for 100,000 times,
and it was much faster.
So I'm sure calling AutoWrap is the bottleneck, but I have no choice but
using it.
Or if anybody knows how to retrieve "ID" property with array, just like we
can do for "Value" property, it will be the best.

Thanks in advance,

With BR,
Daniel Kim

 

[WhytheQ]

Sorry for not getting back to you Daniel. My field is VBA so I can'y
help you I'm afraid.
What language is this code?
Have you tried any other groups?

J

 

[Daniel Kim]

Dear J

Thanks for reply. It is common Win32 code.
First time I have tried with VBA, and it was much slower, so I have moved to
Win32.
It is faster, but not enough.
I guess I need to find some other way. Thanks anyway!

With BR,
Daniel Kim