Retrieve Excel Cell Range into SAFEARRAY (VC++)
 

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.

Retrieve Excel Cell Range into SAFEARRAY (VC++)
 

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.

--
Regards,
Tom Ogilvy

"Henry" wrote in message
ups.com...
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.

Retrieve Excel Cell Range into SAFEARRAY (VC++)
 

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 wrote:
#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.

Retrieve Excel Cell Range into SAFEARRAY (VC++)
 

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.

David Welch wrote:
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 wrote:
#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.

Retrieve Excel Cell Range into SAFEARRAY (VC++)
 

Actually, your problem is he

VARIANT tcvt = range-GetValue();

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

Henry wrote:
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.

David Welch wrote:

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 wrote:
#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.

Retrieve Excel Cell Range into SAFEARRAY (VC++)
 

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/


Henry wrote:
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.

David Welch wrote:

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 wrote:
#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.

Retrieve Excel Cell Range into SAFEARRAY (VC++)
 

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.