Reading a Cell from an Excel sheet embedded in a Word Document

[Herriott@discussions.microsoft.com Jonathan Herriott]

I am having a lot of trouble doing this for some reason. I am able to get
the worksheet, but when I do the following, it crashes:

_variant_t row;
_variant_t col;
row.intVal = 1;
col.intVal = 1;
_bstr_t cellText = pSheet->Cells(row,col)
wcout << wstring(pSheet->Cells(row, col).bstrVal) << endl;

pSheet is the worksheet that I want to read the cells from. I am able to
get the name of the sheet and everything. I just can't do anything with the
cells.

Basically, trying to access anything about rows or columns crashes it. I'm
trying to fix someone else's code, and I usually don't program on a windows
machine, so any help would be great.

Thanks,

Jonathan Herriott

[Jonathan Herriott]

I forgot to mention that this is not .NET, It's using VC++ 6.0

[Alex Blekhman]

Jonathan Herriott wrote:
> I am having a lot of trouble doing this for some reason.
> I am able to get the worksheet, but when I do the
> following, it crashes:
>
> _variant_t row;
> _variant_t col;
> row.intVal = 1;
> col.intVal = 1;
> _bstr_t cellText = pSheet->Cells(row,col)
> wcout << wstring(pSheet->Cells(row, col).bstrVal) << endl;

From one side you're using compiler COM support classes like
_variant_t and _bstr_t, from the other side you're defeting
the whole purpose of these wrappers by accessing and
assigning their values directly. These wrappers are written
with the purpose to hide pesky intVal's and bstrVal's. So,
basically, you should use them as actual values:

_variant_t row = 1L;
_variant_t col = 1L;

// Following statement will throw exception if return
// value of Cells is not convertable to _bstr_t.
//
_bstr_t cellText = pSheet->Cells(row, col);

// Better to write operator << for _bstr_ type, but
// simple cast will work, as well.
//
wcout << (LPCWSTR)cellText << endl;

I recon that Cells() can return either collection of cells
or single cell. So, it won't be convertable to _bstr_t if
collection is returned.

[Jonathan Herriott]

Ok, so I converted the 1's to 1L's (didn't know about the L's, I'm not used
to Windows programming), let me take out as much as possible.

_variant_t row = 1L;
_variant_t col = 1L;

pSheet->Cells(row,col)

This causes the same error I've been having:

COM ERROR: Member not found.

Here's the declaration of Cells:

_variant_t__thiscall Worksheet::Cells(const _variant_t &RowIndex =
vtMissing, const _variant_t &ColumnIndex)

Cells cannot return a collection because it takes the values of row and
column. Range is instead used to access a collection of cells. I get the
same error with Range when I try to use it. The thing I don't understand is
how pSheet can exist and return it's name value, but I can't access any of
it's cells.

This works fine and prints the name of the sheet out:

_bstr_t str2 = pSheet->GetName();
wcout << wstring(str2) << endl;

Hopefully that explains my problem much further.

Thanks,

Jonathan Herriott

[Jonathan Herriott]

"Jonathan Herriott" wrote:

> Ok, so I converted the 1's to 1L's (didn't know about the L's, I'm not used
> to Windows programming), let me take out as much as possible.

Well, I actually had this explained to me and did some research on it. I
should be saying that I've not done unicode programming before. I just
wanted to clarify that.

[Alex Blekhman]

Jonathan Herriott wrote:
> Ok, so I converted the 1's to 1L's (didn't know about the
> L's, I'm not used to Windows programming), let me take
> out as much as possible.

Actually, L suffix is C/C++ language thing. When put after
integer number it specifies long value.

> _variant_t row = 1L;
> _variant_t col = 1L;
>
> pSheet->Cells(row,col)
>
> This causes the same error I've been having:
>
> COM ERROR: Member not found.
>
> Here's the declaration of Cells:
>
> _variant_t__thiscall Worksheet::Cells(const _variant_t
> &RowIndex = vtMissing, const _variant_t &ColumnIndex)

It can't be real declaration. C++ prohibits parameters
without default value after parameters with default value. I
don't have now Office SDK at hand, so I just opened
EXCEL.EXE with OLE/COM Viewer utility. There is _Worksheet
interface with Cells property, which returns Range*. Most of
the chances that #import directive generates ColumnIndex
parameter with default value vtMissing, as well. Default
parameters imply that Cells can be called without specifying
row and column. So, I assume that it returns Range that
includes whole worksheet.

Then you use Range interface, which has Item method to get
single cell.

> Cells cannot return a collection because it takes the
> values of row and column. Range is instead used to
> access a collection of cells. I get the same error with
> Range when I try to use it. The thing I don't understand
> is how pSheet can exist and return it's name value, but I
> can't access any of it's cells.

Probably you're reading Excel SDK for VB[A]. It can be
slightly different than actual interfaces generated for C++
by #import. You should always look at resulting .TLH/.TLI
files to figure out methods and parameters.

> This works fine and prints the name of the sheet out:
>
> _bstr_t str2 = pSheet->GetName();
> wcout << wstring(str2) << endl;

You don't need to call GetName(), instead you can use Name
property directly (thanks to generated wrappers):

wcout << (LPCWSTR)(pSheet->Name) << endl;

[Alex Blekhman]

You're confusing here two things:

1. L (or l) suffix after number, which specifies that the
number is long integer.

See "C++ Integer Constants"
http://msdn.microsoft.com/library/en-us/vclang/html/_pluslan
g_C.2b2b_.Integer_Constants.asp

2. L prefix before string literal, which specifies that the
string is comprised of wide characters (instead of regular
ANSI characters).

See "C++ String Literals"
http://msdn.microsoft.com/library/en-us/vclang/html/_pluslan
g_C.2b2b_.String_Literals.asp

_variant_t class doesn't have constructor for int. But it
does have constructor for long. That's why I wrote
"_variant_t col = 1L". Otherwise compilation would fail
because there is no _variant_t constructor that takes int.

[Jonathan Herriott]

Ok, so let me show what I've gotten from this so far.

Excel::WorkbookPtr oBook;
Excel::WorksheetPtr pSheet;
Excel::RangePtr pRange;

OLEFormatPtr olefmt = shape->GetOLEFormat();
olefmt->Activate();
oBook = olefmt->GetObject();
pSheet = oBook->ActiveSheet;

_variant_t row = 1L;
_variant_t col = 1L;

pRange = pSheet->Range(); // dies here

Since it returns a range, I thought I'd try and get the range of the entire
worksheet. It still dies during execution when doing so. Also, I could not
find the _Worksheet interface you were talking about.

Thanks,
Jonathan Herriott

[Alex Blekhman]

Could you post small working console program that exposes
the problem? I don't have any idea about how you imported
Excel objects into the program. Obviously, you're trying to
use wrong members/properties. Do _not_ rely on IntelliSense
drop down box. It can be misleading quite often. Always look
in actual .tlh file for class declaration.

[Jonathan Herriott]

Ok, this might be a much better way to "ask" the question. How do I take an
Excel::WorksheetPtr object and get from it the cell and display the cell?
Please provide source code because it's much easier for me to understand.
I'm using the xl5en32.olb file as the Excel dependency.

Thanks,
Jonathan Herriott

[Alex Blekhman]

Here's the sample code how to read cell value from Excel
sheet. I created "Sample.xls" file with A1, B1 cells
containing "Hello" and "World" text, respectively. I used MS
Office 2003.

-------
#include <stdio.h>
#include <crtdbg.h>
#include <tchar.h>

#import "C:\\Program Files\\Common Files\\Microsoft
Shared\\OFFICE11\\MSO.DLL" \
rename("RGB", "ExclRGB") \
rename("DocumentProperties", "ExclDocumentProperties") \
rename("SearchPath", "ExclSearchPath")

#import "C:\\Program Files\\Common Files\\Microsoft
Shared\\VBA\\VBA6\\VBE6EXT.OLB"

#import "C:\\Program Files\\Microsoft
Office\\OFFICE11\\EXCEL.EXE" \
rename("DialogBox", "ExclDialogBox") \
rename("RGB", "ExclRGB") \
rename("CopyFile", "ExclCopyFile") \
rename("ReplaceText", "ExclReplaceText")

void PrintCells(const Excel::_WorksheetPtr& ptrWrkSheet)
{
_putws(ptrWrkSheet->Name);

const long nRow = 1L;

for(long nCol = 1L; nCol <= 2L; ++nCol)
{
const _variant_t& vtA1Val =
ptrWrkSheet->Cells->Item[nRow][nCol];

wprintf(L"\tCell(%c1) = \"%s\"\n",
L'A' + nCol - 1, (LPCWSTR)(_bstr_t)vtA1Val);
}

_putwch(L'\n');
}

int _tmain(int /*argc*/, _TCHAR* /*argv*/[])
{
HRESULT hr = ::OleInitialize(NULL);
_ASSERT(SUCCEEDED(hr));

try
{
Excel::_ApplicationPtr ptrExclApp(
__uuidof(Excel::Application));

Excel::WorkbooksPtr ptrWrkBooks =
ptrExclApp->Workbooks;

Excel::_WorkbookPtr ptrWrkBook = ptrWrkBooks->Open(
L"C:\\TEMP\\Sample.xls");

for(long i = 1; i <= ptrWrkBook->Worksheets->Count;
++i)
{
PrintCells(ptrWrkBook->Worksheets->Item[i]);
}

}
catch(_com_error& e)
{
_tprintf(_T("Error 0x%08X; (%s)\n"),
e.Error(), e.ErrorMessage());
}

::OleUninitialize();

return 0;
}
-------

HTH
Alex

[Jonathan Herriott]

Thank you. That helped out a lot. My assumption is that I was maybe using
too old a version of the Excel library. From researching stuff on the
internet, it appears as though xl5es32.olb is for Windows 95 and below, where
as Excel.exe is for 2002(?) and above. Since my computer has Office 2003 on
it, I'm assuming it would have some issues (lack of interfaces that I needed).

Thanks,
Jon

[Alex Blekhman]

MS tries to maintain backward compatibility so, usually, a
program, which is written correctly for Excel 95 will work
for Excel 2003, too. If you don't need to support older
versions of Excel, then you can use Excel 2003 libraries/
However, there are chances that you'll use something that
doesn't exist in older versions, so you program will become
not compatible with them.

Here's the article that specifies type libraries for MS
Office applications:

KB238972 - "INFO: Using Visual C++ to Automate Office"
http://support.microsoft.com/default.aspx?scid=kb;en-us;238972