Retrieve Excel Cell Range into SAFEARRAY (VC++)

[Henry]

Hi all,

I am very new to Excel Programming and COM/Automation so please forgive
any amateur or foolish questions.

I am trying to extract the values of a range cells from a workbook. I
have successfully opened the workbook and can iterate the worksheets. I
have been able to extract the value of a single cell but to iterate
through all the cells in a workbook is not an option as this is quite
resource intensive. So, I assume there is a way I can load a range of
cells into a SAFEARRAY. The SAFEARRAY being held in a VARIANT.

This is the code that I have (error checking is only displayed where it
failed but I am checking all return values):

#import "C:\Program Files\Microsoft Office\Office\MSO9.DLL"
#import "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB"
#import "C:\\Program Files\\Microsoft Office\\Office\\excel9.olb"
rename("DialogBox","DialogBoxXl") rename("RGB","RGBXl") exclude
("IFont","IPicture")

using namespace Excel;

CoInitialize(NULL);
_ApplicationPtr pXl;

hr = pXl.CreateInstance(L"Excel.Application");
WorkbooksPtr pBooks = pXl->Workbooks;

// Open spreadsheet.
pBooks->Open("C:\\Temp\\MySpreadsheet.xls");
WorksheetsPtr pSheets = pXl->GetSheets();

// Get first sheet.
VARIANT vt;
::VariantInit(&vt);
vt.vt = VT_I2;
vt.iVal = (short)1;
_WorksheetPtr thisSheet = pSheets->GetItem(vt);

WCHAR tl[3] = { L'B', L'1', L'\0' };
WCHAR br[3] = { L'B', L'9', L'\0' };
VARIANT cellTL:SysAllocString(tl));
VARIANT cellBR:SysAllocString(br));
RangePtr range = a_sheet->GetRange(cellTL, cellBR);

// The VARIANT is an array of VARIANTs
VARIANT tcvt = range->GetValue();
if (tcvt.vt == (VT_ARRAY | VT_VARIANT))
{
// I know what the min and max col values are but was just
// getting used to the APIs.
SAFEARRAY * sa = tcvt.parray;
long colMax;
hr = ::SafeArrayGetUBound(sa, 1, &colMax);
long colMin;
hr = ::SafeArrayGetLBound(sa, 1, &colMin);

long index[2];
index[0] = 1;
for (long colIdx = colMin; colIdx <= colMax; colIdx++)
{
index[1] = colIdx;
VARIANT thisCell;
::VariantInit(&thisCell);
hr = SafeArrayGetElement(sa, index, &thisCell);
if (FAILED(hr))
{
...
}
}
}

The call to SafeArrayGetElement fails with error code E_INVALIDARG. The
calls to SafeArrayGetUBound are both successful and return what I
expect.
If anyone can offer any insight into why the SafeArrayGetElement fails
it would be greatly appreciated.

Many thanks,
Martin.

 

[Tom Ogilvy]

In VBA it would be

dim v as Variant
v = Workbooks("Sheet1").Range("A1:F30").Value

then v would be an a 2D

v(1 to 30, 1 to 6) array.

 

[David Welch]

You could try using the SafeArrayAccessData and SafeArrayUnaccessData
functions. That way you can get the whole thing at once and its faster.

Also, is the lower bound of the safearray zero or one? It may be zero

 

[Henry]

David,

I have tried using these and they both succeed. But I am unsure as to
how to access the array returned by SafeArrayAccessData. Here is the
code I am using:

// Array obtained as per code snippet from previous post.
SAFEARRAY * sa = tcvt.parray;

// Upper and lower bounds retrieved
....

// The call succeeds so error checking not included.
VARIANT * myX = NULL;
hr = ::SafeArrayAccessData(sa, (void HUGEP * FAR*)&myX);

// This is what I am unsure of. How do I access the elements of the
// array. Just using '[]' operator ? When I attempt to do this I get
// an unknown exception so I guess I am accessing it incorrectly. So,
// the following causes the exception to be thrown.
if (VT_BSTR == myX[0].vt)
{
}

// Call succeeds.
hr = ::SafeArrayUnaccessData(sa);

From the documentation that I have read I know this is faster as the

array is locked once and not every time I access it - so if I can get
it working I would use this approach. However, it would be useful to
know why the SafeArrayGetElement is failing as well but thanks for the
advice.

Thanks,
Martin.

You could try using the SafeArrayAccessData and SafeArrayUnaccessData
functions. That way you can get the whole thing at once and its faster.

Also, is the lower bound of the safearray zero or one? It may be zero

#import "C:\Program Files\Microsoft Office\Office\MSO9.DLL"
#import "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB"
#import "C:\\Program Files\\Microsoft Office\\Office\\excel9.olb"
rename("DialogBox","DialogBoxXl") rename("RGB","RGBXl") exclude
("IFont","IPicture")

using namespace Excel;

CoInitialize(NULL);
_ApplicationPtr pXl;

hr = pXl.CreateInstance(L"Excel.Application");
WorkbooksPtr pBooks = pXl->Workbooks;

// Open spreadsheet.
pBooks->Open("C:\\Temp\\MySpreadsheet.xls");
WorksheetsPtr pSheets = pXl->GetSheets();

// Get first sheet.
VARIANT vt;
::VariantInit(&vt);
vt.vt = VT_I2;
vt.iVal = (short)1;
_WorksheetPtr thisSheet = pSheets->GetItem(vt);

WCHAR tl[3] = { L'B', L'1', L'\0' };
WCHAR br[3] = { L'B', L'9', L'\0' };
VARIANT cellTL:SysAllocString(tl));
VARIANT cellBR:SysAllocString(br));
RangePtr range = a_sheet->GetRange(cellTL, cellBR);

// The VARIANT is an array of VARIANTs
VARIANT tcvt = range->GetValue();
if (tcvt.vt == (VT_ARRAY | VT_VARIANT))
{
// I know what the min and max col values are but was just
// getting used to the APIs.
SAFEARRAY * sa = tcvt.parray;
long colMax;
hr = ::SafeArrayGetUBound(sa, 1, &colMax);
long colMin;
hr = ::SafeArrayGetLBound(sa, 1, &colMin);

long index[2];
index[0] = 1;
for (long colIdx = colMin; colIdx <= colMax; colIdx++)
{
index[1] = colIdx;
VARIANT thisCell;
::VariantInit(&thisCell);
hr = SafeArrayGetElement(sa, index, &thisCell);
if (FAILED(hr))
{
...
}
}
}

The call to SafeArrayGetElement fails with error code E_INVALIDARG. The
calls to SafeArrayGetUBound are both successful and return what I
expect.
If anyone can offer any insight into why the SafeArrayGetElement fails
it would be greatly appreciated.

Many thanks,
Martin.

 

[David Welch]

Actually, your problem is here:

VARIANT tcvt = range->GetValue();

if you change the VARIANT to a _variant_t then everything is fine!

David,

I have tried using these and they both succeed. But I am unsure as to
how to access the array returned by SafeArrayAccessData. Here is the
code I am using:

// Array obtained as per code snippet from previous post.
SAFEARRAY * sa = tcvt.parray;

// Upper and lower bounds retrieved
...

// The call succeeds so error checking not included.
VARIANT * myX = NULL;
hr = ::SafeArrayAccessData(sa, (void HUGEP * FAR*)&myX);

// This is what I am unsure of. How do I access the elements of the
// array. Just using '[]' operator ? When I attempt to do this I get
// an unknown exception so I guess I am accessing it incorrectly. So,
// the following causes the exception to be thrown.
if (VT_BSTR == myX[0].vt)
{
}

// Call succeeds.
hr = ::SafeArrayUnaccessData(sa);

From the documentation that I have read I know this is faster as the

array is locked once and not every time I access it - so if I can get
it working I would use this approach. However, it would be useful to
know why the SafeArrayGetElement is failing as well but thanks for the
advice.

Thanks,
Martin.

You could try using the SafeArrayAccessData and SafeArrayUnaccessData
functions. That way you can get the whole thing at once and its faster.

Also, is the lower bound of the safearray zero or one? It may be zero

#import "C:\Program Files\Microsoft Office\Office\MSO9.DLL"

#import "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB"
#import "C:\\Program Files\\Microsoft Office\\Office\\excel9.olb"
rename("DialogBox","DialogBoxXl") rename("RGB","RGBXl") exclude
("IFont","IPicture")

using namespace Excel;

CoInitialize(NULL);
_ApplicationPtr pXl;

hr = pXl.CreateInstance(L"Excel.Application");
WorkbooksPtr pBooks = pXl->Workbooks;

// Open spreadsheet.
pBooks->Open("C:\\Temp\\MySpreadsheet.xls");
WorksheetsPtr pSheets = pXl->GetSheets();

// Get first sheet.
VARIANT vt;
::VariantInit(&vt);
vt.vt = VT_I2;
vt.iVal = (short)1;
_WorksheetPtr thisSheet = pSheets->GetItem(vt);

WCHAR tl[3] = { L'B', L'1', L'\0' };
WCHAR br[3] = { L'B', L'9', L'\0' };
VARIANT cellTL:SysAllocString(tl));
VARIANT cellBR:SysAllocString(br));
RangePtr range = a_sheet->GetRange(cellTL, cellBR);

// The VARIANT is an array of VARIANTs
VARIANT tcvt = range->GetValue();
if (tcvt.vt == (VT_ARRAY | VT_VARIANT))
{
// I know what the min and max col values are but was just
// getting used to the APIs.
SAFEARRAY * sa = tcvt.parray;
long colMax;
hr = ::SafeArrayGetUBound(sa, 1, &colMax);
long colMin;
hr = ::SafeArrayGetLBound(sa, 1, &colMin);

long index[2];
index[0] = 1;
for (long colIdx = colMin; colIdx <= colMax; colIdx++)
{
index[1] = colIdx;
VARIANT thisCell;
::VariantInit(&thisCell);
hr = SafeArrayGetElement(sa, index, &thisCell);
if (FAILED(hr))
{
...
}
}
}

The call to SafeArrayGetElement fails with error code E_INVALIDARG. The
calls to SafeArrayGetUBound are both successful and return what I
expect.
If anyone can offer any insight into why the SafeArrayGetElement fails
it would be greatly appreciated.

Many thanks,
Martin.

 

[David Welch]

Also, this is a much better way of doing this, which is to use a library
where someone has already done the work for you. See

http://www.cuj.com/documents/s=9765/cuj9904alexandr/

David,

I have tried using these and they both succeed. But I am unsure as to
how to access the array returned by SafeArrayAccessData. Here is the
code I am using:

// Array obtained as per code snippet from previous post.
SAFEARRAY * sa = tcvt.parray;

// Upper and lower bounds retrieved
...

// The call succeeds so error checking not included.
VARIANT * myX = NULL;
hr = ::SafeArrayAccessData(sa, (void HUGEP * FAR*)&myX);

// This is what I am unsure of. How do I access the elements of the
// array. Just using '[]' operator ? When I attempt to do this I get
// an unknown exception so I guess I am accessing it incorrectly. So,
// the following causes the exception to be thrown.
if (VT_BSTR == myX[0].vt)
{
}

// Call succeeds.
hr = ::SafeArrayUnaccessData(sa);

From the documentation that I have read I know this is faster as the

array is locked once and not every time I access it - so if I can get
it working I would use this approach. However, it would be useful to
know why the SafeArrayGetElement is failing as well but thanks for the
advice.

Thanks,
Martin.

You could try using the SafeArrayAccessData and SafeArrayUnaccessData
functions. That way you can get the whole thing at once and its faster.

Also, is the lower bound of the safearray zero or one? It may be zero

#import "C:\Program Files\Microsoft Office\Office\MSO9.DLL"

#import "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6EXT.OLB"
#import "C:\\Program Files\\Microsoft Office\\Office\\excel9.olb"
rename("DialogBox","DialogBoxXl") rename("RGB","RGBXl") exclude
("IFont","IPicture")

using namespace Excel;

CoInitialize(NULL);
_ApplicationPtr pXl;

hr = pXl.CreateInstance(L"Excel.Application");
WorkbooksPtr pBooks = pXl->Workbooks;

// Open spreadsheet.
pBooks->Open("C:\\Temp\\MySpreadsheet.xls");
WorksheetsPtr pSheets = pXl->GetSheets();

// Get first sheet.
VARIANT vt;
::VariantInit(&vt);
vt.vt = VT_I2;
vt.iVal = (short)1;
_WorksheetPtr thisSheet = pSheets->GetItem(vt);

WCHAR tl[3] = { L'B', L'1', L'\0' };
WCHAR br[3] = { L'B', L'9', L'\0' };
VARIANT cellTL:SysAllocString(tl));
VARIANT cellBR:SysAllocString(br));
RangePtr range = a_sheet->GetRange(cellTL, cellBR);

// The VARIANT is an array of VARIANTs
VARIANT tcvt = range->GetValue();
if (tcvt.vt == (VT_ARRAY | VT_VARIANT))
{
// I know what the min and max col values are but was just
// getting used to the APIs.
SAFEARRAY * sa = tcvt.parray;
long colMax;
hr = ::SafeArrayGetUBound(sa, 1, &colMax);
long colMin;
hr = ::SafeArrayGetLBound(sa, 1, &colMin);

long index[2];
index[0] = 1;
for (long colIdx = colMin; colIdx <= colMax; colIdx++)
{
index[1] = colIdx;
VARIANT thisCell;
::VariantInit(&thisCell);
hr = SafeArrayGetElement(sa, index, &thisCell);
if (FAILED(hr))
{
...
}
}
}

The call to SafeArrayGetElement fails with error code E_INVALIDARG. The
calls to SafeArrayGetUBound are both successful and return what I
expect.
If anyone can offer any insight into why the SafeArrayGetElement fails
it would be greatly appreciated.

Many thanks,
Martin.

 

[Henry]

David,

thanks a million!! I will look into this and try and understand why
this now works. I have been pulled off onto something else but I did
what you said and everything is indeed fine! That was annoying me for
nearly a week and I don't think I would have found it so thanks again.

Cheers,
Martin.